CODEWITHSUNDEEP
entity-frameworkjoin
Antoine
Antoine

Reputation: 5255

How to do a left join in Entity framework?

I'm new to Entity Framework and I have some troubles with a query that includes a join. Here's the model I'm using:
http://img690.imageshack.us/img690/7132/modelow.png

I want to query all models, with the enabled info for a given profile (which I get from the instance info). As first step I'm trying to get all models with the enabled info when it exists (left join), but that fails. I tried the Join method but I can't even get to compile.

var modelList = dc.db.models.Join(dc.db.profile_model,
                y => y.id,
                s => s.models.id,
                (y, s) => new Object( y.id, y.display, s.enabled, s.models, s.profiles )
                ))

MSDN about this function does not correspond to what intellisense gives me, and I have troubles with the parameters. I think the first 3 ones are ok (2nd table and the 2 keys on which to join), but I can't get the last one correctly. How am I supposed to get an object that contains all fields of both models and profile_model?

Thanks

Update:
To be clearer, here's the query as written in plain good old SQL:

select m.*, p.enabled from models m
left join profile_model p on m.id = p.model_id
where p.profile_id = 
(
  select profile_id from instances where instances.display = 'PTXM11602' 
)
or profile_id is null

How would it be written using e-f?

Upvotes: 2

Views: 452

Answers (1)

Steven
Steven

Reputation: 172835

If I understand you correctly, you want a list of models with per model all instances of that model. When you designed your model correctly and have all correct referential keys in the database, you should be able to write something like this:

var models =
    from model in db.models
    where (
        from pm in model.profile_models
        from instance in pm.profile.instances
        where instance.display == "PTXM11602").Any()
      ||
        !model.profile_models.Any()
    select model;

var results =
    from pm in db.profile_models
    where pm.profile.instances.Any(i => i.display == "PTXM11602")
    where models.Contains(pm.model)
    select new 
    {
        Model = pm.model,
        Enabled = pm.enabled
    };

Please read this MSDN forum to get the Contains method to work with Entity Framework 3.5.

Upvotes: 2

Related Questions