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












0














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?










share|improve this question






















  • Just use the navigation properties and it will create all the needed joins.
    – juharr
    Nov 12 '18 at 17:07










  • "But obviously database.ExperimentExperimentTypes in not recognized in code."; if the table is there, you can define it in code. That way it is accessible. Depending on your query, and doing it correctly, you can boost performance that way.
    – Stefan
    Nov 12 '18 at 17:14










  • (in addition to my previous comment): or do what @juharr said ;-)
    – Stefan
    Nov 12 '18 at 17:26
















0














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?










share|improve this question






















  • Just use the navigation properties and it will create all the needed joins.
    – juharr
    Nov 12 '18 at 17:07










  • "But obviously database.ExperimentExperimentTypes in not recognized in code."; if the table is there, you can define it in code. That way it is accessible. Depending on your query, and doing it correctly, you can boost performance that way.
    – Stefan
    Nov 12 '18 at 17:14










  • (in addition to my previous comment): or do what @juharr said ;-)
    – Stefan
    Nov 12 '18 at 17:26














0












0








0







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?










share|improve this question













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?







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






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 12 '18 at 17:03









Guido Lo Spacy

111115




111115












  • Just use the navigation properties and it will create all the needed joins.
    – juharr
    Nov 12 '18 at 17:07










  • "But obviously database.ExperimentExperimentTypes in not recognized in code."; if the table is there, you can define it in code. That way it is accessible. Depending on your query, and doing it correctly, you can boost performance that way.
    – Stefan
    Nov 12 '18 at 17:14










  • (in addition to my previous comment): or do what @juharr said ;-)
    – Stefan
    Nov 12 '18 at 17:26


















  • Just use the navigation properties and it will create all the needed joins.
    – juharr
    Nov 12 '18 at 17:07










  • "But obviously database.ExperimentExperimentTypes in not recognized in code."; if the table is there, you can define it in code. That way it is accessible. Depending on your query, and doing it correctly, you can boost performance that way.
    – Stefan
    Nov 12 '18 at 17:14










  • (in addition to my previous comment): or do what @juharr said ;-)
    – Stefan
    Nov 12 '18 at 17:26
















Just use the navigation properties and it will create all the needed joins.
– juharr
Nov 12 '18 at 17:07




Just use the navigation properties and it will create all the needed joins.
– juharr
Nov 12 '18 at 17:07












"But obviously database.ExperimentExperimentTypes in not recognized in code."; if the table is there, you can define it in code. That way it is accessible. Depending on your query, and doing it correctly, you can boost performance that way.
– Stefan
Nov 12 '18 at 17:14




"But obviously database.ExperimentExperimentTypes in not recognized in code."; if the table is there, you can define it in code. That way it is accessible. Depending on your query, and doing it correctly, you can boost performance that way.
– Stefan
Nov 12 '18 at 17:14












(in addition to my previous comment): or do what @juharr said ;-)
– Stefan
Nov 12 '18 at 17:26




(in addition to my previous comment): or do what @juharr said ;-)
– Stefan
Nov 12 '18 at 17:26












1 Answer
1






active

oldest

votes


















0














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. He doesn't want to join tables, he wants all (or some)Experiments, each with all (or some of) 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.






share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53266879%2flinq-to-entities-perform-joins-on-many-to-many-relationships-code-first%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    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. He doesn't want to join tables, he wants all (or some)Experiments, each with all (or some of) 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.






    share|improve this answer


























      0














      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. He doesn't want to join tables, he wants all (or some)Experiments, each with all (or some of) 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.






      share|improve this answer
























        0












        0








        0






        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. He doesn't want to join tables, he wants all (or some)Experiments, each with all (or some of) 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.






        share|improve this answer












        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. He doesn't want to join tables, he wants all (or some)Experiments, each with all (or some of) 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.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 14 '18 at 13:17









        Harald Coppoolse

        11.5k12959




        11.5k12959






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53266879%2flinq-to-entities-perform-joins-on-many-to-many-relationships-code-first%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            這個網誌中的熱門文章

            Xamarin.form Move up view when keyboard appear

            Post-Redirect-Get with Spring WebFlux and Thymeleaf

            Anylogic : not able to use stopDelay()