Unable to retrieve Distinct record from database?

I have a Database table like below,

ID Author  Book

1  Kate    Smuggler

2  Harper  Smuggler

3  Slater  Smuggler

4  Kate   Katy

5  Smitha  Katy

i want to retrieve all Book names(Distinct) based on the author name and display them on list view, i wrote the code below to Retrieve distinct values

int UserID = Convert.ToInt32(Session["User"]);

var Li = (from s in Data.TableName where s.Author == UserID select s.Book).Distinct().OrderBy(ID=>ID);

                    ListViewChatList.DataSourceID = "";
                    ListViewChatList.DataSource = Li;
                    ListViewChatList.DataBind();

but i get the following error 'System.String' does not contain a property with the name 'ID'.

How to solve this ?

Jon Skeet
people
quotationmark

This part:

select s.Book

is selecting just the book (title) part of the record. You can't get back from that to the ID... there can easily be multiple IDs for the same title, as per your sample data.

It's not clear what you'd want that query to return anyway, given that you're getting distinct book titles - ignoring the author, would you want IDs 1 and 4? 2 and 5? 3 and 5? Both represent the same set of distinct titles (Smuggler and Katy).

You could potentially group by name, and then do whatever you want with the IDs for the books with the shared name - but you really need to consider what your distinctness criterion means for the results.

Here's a query which will give you the lowest ID for each name:

var query = from s in Data.TableName
            where s.Author == UserID
            group s.ID by s.Book into g
            select g.OrderBy(id => id).First();

It's not clear whether that's what you really need though.

people

See more on this question at Stackoverflow