Deal of the Day

Home » Main » Manning Forums » 2007 » LINQ in Action

Thread: Ch4 left join query

Reply to this Thread Reply to this Thread Search Forum Search Forum Back to Thread List Back to Thread List

Permlink Replies: 7 - Pages: 1 - Last Post: Apr 23, 2012 1:02 PM by: swartzbill2000@... Threads: [ Previous | Next ]
sjoshi

Posts: 6
From: USA
Registered: 2/25/08
Ch4 left join query
Posted: Feb 25, 2008 8:54 PM
  Click to reply to this thread Reply

I get a message saying
{"DataBinding: 'System.Char' does not contain a property with the name 'Title'."}

When I run this query on a web page

from publisher in SampleData.Publishers
join book in SampleData.Books
on publisher equals book.Publisher into PublisherBooks
from book in PublisherBooks.DefaultIfEmpty()
select new
{
Publisher = publisher.Name,
Books = book == default(Book) ? "(no books)" : book.Title
};

Any ideas what might be wrong here ?

thanks
Sunit

sjoshi

Posts: 6
From: USA
Registered: 2/25/08
Re: Ch4 left join query
Posted: Feb 25, 2008 9:14 PM   in response to: sjoshi in response to: sjoshi
  Click to reply to this thread Reply

Ok looks lie this happens since I'm using the GridView with these Columns

<asp:BoundField DataField="Publisher" HeaderText="Publisher" />
<asp:TemplateField HeaderText="Books">
<ItemTemplate>
<asp:BulletedList ID="BulletedList" runat="server"
DataValueField="Title" DataSource='<%#Eval("Books") %>' />
</ItemTemplate>
</asp:TemplateField>

And it's looking for the type Book and it's member Title. So to fix that I tried this:

from publisher in SampleData.Publishers
join book in SampleData.Books
on publisher equals book.Publisher into PublisherBooks
from book in PublisherBooks.DefaultIfEmpty(new Book { Title = "(no books)" })
select new
{
Publisher = publisher.Name,
Books = book
};

But that does not work and gives this error

{"Data source is an invalid type. It must be either an IListSource, IEnumerable, or IDataSource."}

Although the ResultsView of the query in debug shows the correct entry as
{ Publisher = "I Publisher", Books = {(no books)} }

Any ideas ??

thanks
Sunit

fabrice.marguerie


Posts: 225
From: France
Registered: 4/28/06
Re: Ch4 left join query
Posted: Feb 26, 2008 2:29 AM   in response to: sjoshi in response to: sjoshi
  Click to reply to this thread Reply

Hello Sunit,

Your mistake is that the query in Listing 4.26 (left outer join) is the following:

from publisher in SampleData.Publishers
join book in SampleData.Books
on publisher equals book.Publisher into PublisherBooks
from book in PublisherBooks.DefaultIfEmpty()
select new
{
Publisher = publisher.Name,
Book = book == default(Book) ? "(no books)" : book.Title
};

and not as you write:

from publisher in SampleData.Publishers
join book in SampleData.Books
on publisher equals book.Publisher into PublisherBooks
from book in PublisherBooks.DefaultIfEmpty()
select new
{
Publisher = publisher.Name,
Books = book == default(Book) ? "(no books)" : book.Title
};


The query returns pairs of Publisher and Book strings, not a publisher and its books.

It looks like what you want to achieve is a group join, as demonstrated in Listing 4.23:

from publisher in SampleData.Publishers
join book in SampleData.Books
on publisher equals book.Publisher into publisherBooks
select new { Publisher=publisher.Name, Books=publisherBooks };


As you can see in the source code archive, we use the markup you're trying to use with the above query (in Joins.aspx):

<asp:GridView ID="GridViewGroupJoin" AutoGenerateColumns="false" runat="server">
<Columns>
<asp:BoundField HeaderText="Publisher" DataField="Publisher" />
<asp:TemplateField HeaderText="Books">
<ItemTemplate>
<asp:BulletedList ID="BulletedList1" runat="server"
DataSource='<% #Eval("Books") %>' DataValueField="Title" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>


Fabrice

sjoshi

Posts: 6
From: USA
Registered: 2/25/08
Re: Ch4 left join query
Posted: Feb 26, 2008 7:08 PM   in response to: fabrice.marguerie in response to: fabrice.marguerie
  Click to reply to this thread Reply

Thanks Fabrice. I had deliberately changed that since I want a Book[] type and not just string type. I was trying to figure out how to change the query to be able to still use with the first GridView with this template (below) but seems I'm failing:


<asp:GridView ID="GridViewGroupJoin" AutoGenerateColumns="false" runat="server">
<Columns>
<asp:BoundField HeaderText="Publisher" DataField="Publisher" />
<asp:TemplateField HeaderText="Books">
<ItemTemplate>
<asp:BulletedList ID="BulletedList1" runat="server"
DataSource='<% #Eval("Books") %>' DataValueField="Title" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

thanks
Sunit

fabrice.marguerie


Posts: 225
From: France
Registered: 4/28/06
Re: Ch4 left join query
Posted: Feb 27, 2008 3:52 AM   in response to: sjoshi in response to: sjoshi
  Click to reply to this thread Reply

If you use a left outer join, your query returns pairs of publisher name (string) and book title (string). In the query below, Book is not an enumeration, but a string. This means that you can't use this property as a DataSource.

from publisher in SampleData.Publishers
join book in SampleData.Books
on publisher equals book.Publisher into PublisherBooks
from book in PublisherBooks.DefaultIfEmpty()
select new
{
Publisher = publisher.Name,
Book = book == default(Book) ? "(no books)" : book.Title
};


In this case, here is the markup you need:

<asp:GridView ID="GridViewGroupJoin" AutoGenerateColumns="false" runat="server">
<Columns>
<asp:BoundField HeaderText="Publisher" DataField="Publisher" />
<asp:BoundField HeaderText="Book" DataField="Book" />
</Columns>
</asp:GridView>


If you want to get a collection of books for each publisher, then you use a group join as in Listing 4.23 and you keep the markup and query as is.

sjoshi

Posts: 6
From: USA
Registered: 2/25/08
Re: Ch4 left join query
Posted: Feb 27, 2008 7:47 AM   in response to: fabrice.marguerie in response to: fabrice.marguerie
  Click to reply to this thread Reply

Thanks. So won't this query yield an Enumerable for Books

var query = from publisher in SampleData.Publishers
join book in SampleData.Books
on publisher equals book.Publisher into PublisherBooks
from book in PublisherBooks.DefaultIfEmpty(new Book { Title = "(no books)" })
select new
{
Publisher = publisher.Name,
Books = book
};

And the wierd thing is, VS debug view does show me that as the last row of the query variable. I guess I'm missing something here and need to do more reading (:

thanks again
Sunit

fabrice.marguerie


Posts: 225
From: France
Registered: 4/28/06
Re: Ch4 left join query
Posted: Feb 27, 2008 8:47 AM   in response to: sjoshi in response to: sjoshi
  Click to reply to this thread Reply

A from clause enumerates the content of the source indicated after the in keyword.
When you write this:
from book in PublisherBooks.DefaultIfEmpty(new Book { Title = "(no books)" })
the source is a sequence that contains Book objects if the publisher has books, and a sequence of exactly one Book object with Title = "(no books)" if the publisher has no books. Your from and select clauses take each book in these sequences and project them into an anonymous type that groups a Publisher name and a Book object.

It looks like what you are trying to achieve is this:

from publisher in SampleData.Publishers
join book in SampleData.Books
on publisher equals book.Publisher into publisherBooks
select new
{
Publisher = publisher.Name,
Books = publisherBooks.DefaultIfEmpty(new Book { Title = "(no books)" })
}


The above should work for you.
However, personally I would keep the query as in listing 4.23 (group join) and handle the special case where the DataSource is empty using code-behind, instead of creating an artificial "(no books)" book.

swartzbill2000@...

Posts: 1
From: New Mexico
Registered: 4/22/12
Re: Ch4 left join query
Posted: Apr 23, 2012 1:02 PM   in response to: fabrice.marguerie in response to: fabrice.marguerie
  Click to reply to this thread Reply

Here is my 2 cents worth for using method syntax. For some reason, this makes the most sense to me.

GridViewLeftOuterJoin.DataSource
= SampleData.Publishers
.SelectMany(
pub => SampleData.Books
.Where(bk => bk.Publisher == pub)
.DefaultIfEmpty(new Book { Title = "(no books)" })
.Select(bk => new { Publisher = pub.Name, Book = bk.Title })
);
GridViewLeftOuterJoin.DataBind();

Legend
Gold: 300 + pts
Silver: 100 - 299 pts
Bronze: 25 - 99 pts
Manning Author
Manning Staff
Manning Developmental Editor