LINQ to Entities: perform joins on many-to-many relationships (code first)

user_1251784 0
Viewed 1084

I have the following model:

[Table("Experiments")]
public class Experiment
{
    ...

    public virtual ICollection<ExperimentType> ExperimentTypes { get; set; }

    public Experiment()
    {
        ExperimentTypes = new List<ExperimentType>();
    }
}

[Table("ExperimentTypes")]
public class ExperimentType
{
    ...

    public virtual ICollection<Experiment> Experiments { get; set; }

    public ExperimentType()
    {
        Experiments = new List<Experiments>();
    }
}

The DbSet contains:

    public DbSet<Experiment> Experiments { get; set; }
    public DbSet<ExperimentType> ExperimentTypes{ get; set; }

And this creates a table on SQL, called ExperimentExperimentTypes.

Now, I would like to perform a LINQ join, like:

var query =
   from e in database.Experiments
   join eet in database.ExperimentExperimentTypes on eet.Experiment_Id equals eet.ExperimentType_Id ...

But obviously database.ExperimentExperimentTypes in not recognized in code.

I tried a lot of things in order to tell the code that there is this table, I also tried to create the corresponding c# class, but I'm not getting any result.

How can achieve that?

ef-code-first code-first linq-to-entities linq c#
1 Answers

So you have two tables: Experiment and ExperimentType. There is a many-to-many relation between those two: every Experiment is an experiment of zero or more ExperimentTypes; every ExperimentType is the type of zero or more Experiments.

This many-to-many can be seen in your class definitions. The virtual ICollection<...> on both sides indicates the many-to-many relationship.

In relational databases this many-to-many relation is implemented using a junction table. However, in entity framework you seldom see the junction table. It is in your database, however, you can't access it using your DbContext

But how am I going to perform a join between Experiments and ExperimentTypes if I can't access the junction table?

Well, Pooh bear should go back to his thinking spot. You don't want to join tables, you want Experiments, each with its ExperimentTypes.

So why not just do the query using the ICollection<...>?

var experiments = myDbContext.Experiments
    .Where(experiment => ...)              // only if you don't want all experiments
    .Select(experiment => new
    {   // Select only the properties you actually plan to use
        Id = experiment.Id,
        Name = experiment.Name,
        ...

        // get all or some of its ExperimentTypes
        ExperimentTypes = experiment.ExperimentTypes
             .Where(experimentType => ...)  // only if you don't want all experiment types
             .Select(experimentType => new
             {
                  // again: select only the properties you plan to use
                  Id = experimentType.Id,
                  ...
             })
             .ToList(),
    });

Entity framework knows the many-to-many, it knows that for this a triple join with the junction table is needed, and will perform this triple join.

Internally this will be a GroupJoin, you'll get Experiments, each with their ExperimentTypes. You even get Experiments that don't have any ExperimentType yet.

If you really want the inner join, you'll have to flatten the Experiments with their ExperimentTypes. This is done using the overload of SelectMany that has a resultSelector as parameter

// flatten the Experiments with their experimentTypes
var flatInnerJoin = myDbContext.Experiments.SelectMany(experiment => experiment.ExperimentTypes,

    // from each experiment and one of its experimentTypes make one new object
    (experiment, experimentType) => new
    {
        ExperimentId = experiment.Id,
        ExperimentTypeId = experimentType.Id,
        ...
     });
 })

Nota bene! This way you won't get the Experiments that have no ExperimentTypes, just as in a standard inner join.

user_22817900
Related