Problems with LINQ to SQL

Question

I am trying to use the following LINQ to SQL code 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 is in the second line of the connection (the 3rd line in the entire request above) - I used it to just join s.ID and pop.AccountID, and it worked fine, but now I presented one more criterion for joining (syncno) I I get the following error:

"The type of one of the expressions in the join clause is incorrect. The type could not complete the output in 'GroupJoin'"

Any ideas? Some notes:

1: the "syncNo" variable is long, like the value in the DB (bigint). The value in db is zero, so I also tried "long?" As the type of the variable

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

thanks

Answer 1

Try specifying the same connection key names, for example.

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

Answer 2

In the MSDN docs:

Type inference for 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 name, you must assign new names in the keys. For example, if the Orders table and the OrderDetails table use different names for their columns, you can create composite keys by assigning the same names in 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 compare properties of different types . For example, comparing short with int or string with int, etc. The names must match, but if your comparison already has the same name and you cannot find the problem, 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 compares with an integer , it will also give you a message:

"The type of one of the expressions in the join clause is invalid. Type inference failed when calling" GroupJoin ""