LINQ to sharepoint . join list help

I am currently using SharePoint 2010,and in my business layer I am using LINQ to SharePoint. I generated all my Entity classes using SPMetal.

We are creating a Library system, my system has 2 Lists . The first one is Contribution and the second one is Contributor. Each Contributor contains a reference to the Contribution List (a PrimaryISBN reference). The Contribution List contains list of books and PrimaryISBN is not unique in this list.

Contribution

ID  PrimaryISBN      TITLE      
1   PRIM1            HardcoverLOTR      
2   PRIM1            AudioBookLOTR      
3   PRIM2            HardcoverHP        

Contributor

ID  Name  PrimaryISBNLookup
1   ABC   PRIM1
2   DEF   PRIM2

I am currently trying to fetch All the Books Contributed by a Particular user based on the Name. My query is something like this

var result = from _contributor in data.contributor
             where _contributor.Name= "ABC"
             select new Book
            {
      开发者_如何学运维         Title = contributor.PrimaryISBNLookup.Title
            }

The problem that I am currently facing is in retrieving records that have same ISBN but different title (Each format will have a title i.e. a Audio book will have a title and a Hardcover of the same book will have a different one). This query returns me only 1 records even thought I have 2 records in my system i.e. the record with ID (in Contribution) that I am forced to insert during the insertion of record into Contributor List.

Your help is highly appreciated.

From what I understand, you try to implement a simple join, like this:

var results = from _contributor in data.contributor
              join _contribution in data.contribution
              on _contributor.PrimaryISBNLookup equals _contribution.PrimaryISBN
              where _contributor.Name == "ABC"
              select new Book
              {
                  Title = _contribution.Title
              }

If you want to use DataTable in SPList, try this:

           SPList  cList = spWeb.Lists.TryGetList("Customer");
           SPList   oList = spWeb.Lists.TryGetList("Order");


            DataTable cTable= cList.Items.GetDataTable();
            DataTable oTable= oList.Items.GetDataTable();

            var coList = from tbl1 in cTable.AsEnumerable()
                         join tbl2 in oTable.AsEnumerable() on tbl1["Title"] equals           tbl2["CustomerName"]
                         select new
                         {
                             ItemName = tbl2["Title"],
                             CustomerName = tbl1["Title"],
                             Mobile = tbl1["MobileNo"]

                         };