|
Replies:
7
-
Pages:
1
-
Last Post:
Apr 23, 2012 1:02 PM
by: swartzbill2000@...
|
Threads:
[
Previous
|
Next
]
|
|
Posts:
6
From:
USA
Registered:
2/25/08
|
|
|
|
Ch4 left join query
Posted:
Feb 25, 2008 8:54 PM
|
|
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
|
|
Posts:
6
From:
USA
Registered:
2/25/08
|
|
|
|
Re: Ch4 left join query
Posted:
Feb 25, 2008 9:14 PM
in response to:
sjoshi
|
|
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
|
|
Posts:
225
From:
France
Registered:
4/28/06
|
|
|
|
Re: Ch4 left join query
Posted:
Feb 26, 2008 2:29 AM
in response to:
sjoshi
|
|
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
|
|
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
|
|
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
|
|
Posts:
225
From:
France
Registered:
4/28/06
|
|
|
|
Re: Ch4 left join query
Posted:
Feb 27, 2008 3:52 AM
in response to:
sjoshi
|
|
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.
|
|
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
|
|
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
|
|
Posts:
225
From:
France
Registered:
4/28/06
|
|
|
|
Re: Ch4 left join query
Posted:
Feb 27, 2008 8:47 AM
in response to:
sjoshi
|
|
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.
|
|
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
|
|
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
|
|
