LINQ to SQL Join issues

Question

I'm trying to use the following LINQ to SQL in my code:

  (from s in dc.Accounts 
  join purchases in dc.Transactions on s.AccID equals purchases.Account into pu
  join pop in dc.POPTransactions on new { s.ID, syncNo } equals new {AccId = pop.AccountID, SyncNo = pop.SyncNo } into po
  where s.AccID == ID && s.Customer == false
  select new AccsandPurchase { acc = s, purchases = pu.ToList(), pop = po.ToList() } ));

The error happens on the second join line (3rd line in the whole query above) - I used to have it so it just joined on s.ID and pop.AccountID and that worked perfect, but now I introduced another join critieria (the syncno) I get the following error:

"The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'GroupJoin'"

Any ideas? Some notes:

1: 'the variable 'syncNo' is a long, as is the value in the DB (bigint). The value in the db is nullable so I've also tried "long?" as the variable type

2: AccsandPurchase is a custom class I made, as you can probably guess

Thanks

Answer 1

Try to specify the same join key names e.g.

join pop in dc.POPTransactions on new { Key1 = s.ID, Key2 = syncNo } equals new {Key1 = pop.AccountID, Key2 = pop.SyncNo }

Answer 2

From the MSDN docs:

Type inference on composite keys depends on the names of the properties in the keys, and the order in which they occur. If the properties in the source sequences do not have the same names, you must assign new names in the keys. For example, if the Orders table and OrderDetails table each used different names for their columns, you could create composite keys by assigning identical names in the anonymous types:

join...on new {Name = o.CustomerName, ID = o.CustID} equals 
   new {Name = d.CustName, ID = d.CustID }

http://msdn.microsoft.com/en-us/library/bb907099.aspx

Answer 3

This problem often occurs when you are comparing the properties of different types as well. Such as comparing a short to an int, or a string to an int, etc. The names must match, but when your comparison already has equal names and you can't find the issue, check if they are also of the same type.

from p in DbSet.AsQueryable()
join t in _dbContext.SomeEntity on new { p.Id, Type = (int)MyEnum.Something }
                            equals new { t.Id, Type = t.EntityType}

In this example, if t.EntityType is short, which is being compared to an integer, it will also give you the message:

"The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'GroupJoin'"