Monday, February 2, 2009

LINQ to Entity Joins

Joins with LINQ-to-Entity are pretty straightforward - you don't have to write join conditions manually!

However, sometimes you do get an error that looks a little like this:

An expression of type PkTableEntity is not allowed in a subsequent from clause in a query expression with source type System.Data.Objects.ObjectQuery<FkTableEntity>. Type inference failed in the call to SelectMany.

It simply means that you've put your tables in the wrong order. I did something like this:

var rset = (from iterFkEntity in objDbEntities.FkTableEntity
from iterPkEntity in iterFkEntity.PkTableEntities
where iterPkEntity.ColumnA == 3
select iterFkEntity);

To correct it, I simply had to flip over the PkTable and the FkTable and turn it into:

var rset = (from iterPkEntity in objDbEntities.PkTableEntity
from iterFkEntity in iterPkEntity.FkTableEntities
where iterPkEntity.ColumnA == 3
select iterFkEntity);

Re-compile and the error is gone!

3 comments:

Pancreatist said...

Thank you, this worked for me. I was wondering what the heck was going on... :P

Nitin R.K. said...

@Pancreatist

I'm glad I could help. BTW, you ought to try out LLBLGen. It's an ORM that acts as a Linq provider and gets around many of the limitations that Linq-to-SQL and Linq-to-Entities impose.

Unknown said...

I was hoping to get your help on this, I am getting the same error, but am not sure how to do it...and I have tried many ways... the
AGREEMENT_PACK table is a reference table between the other 2...

ObjectQuery c2a = db.AGREEMENT;
IQueryable AGREEMENT_IDS =
from com2d in c2a
from com2c in com2d.AGREEMENT_PACK
from com2b in com2c.PACK
from com2a in com2b.ADMINISTRATION where com2a.DESC1.Contains(keyWord) || com2a.DESC_EN.Contains(keyWord)
select com2d.AGREEMENT_ID;

Here are the relation keys

AGREEMENT_PACK.PACHID = PACK.ID <- foreign key
AGREEMENT_PACK.AGREEMENTID = AGREEMENT.ID <- foreign key
PACK.AdminId = ADMINISTRATION.ID <- foreign key