sjoshi (6) [Avatar] Offline
#1
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 (6) [Avatar] Offline
#2
Re: Ch4 left join query
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 (224) [Avatar] Offline
#3
Re: Ch4 left join query
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 (6) [Avatar] Offline
#4
Re: Ch4 left join query
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 (224) [Avatar] Offline
#5
Re: Ch4 left join query
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 (6) [Avatar] Offline
#6
Re: Ch4 left join query
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 (224) [Avatar] Offline
#7
Re: Ch4 left join query
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@yahoo.com (1) [Avatar] Offline
#8
Re: Ch4 left join query
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();