Join table with linq into object-list in class

Question

I have to tables in SQL Database and want to join it with linq. I use Dapper to get to lists.

How do i join this two tables with linq?

    var persons = new List<Persons>();
    var skills = new List<Skills>();

    public class Skill
    {
        public int PerID { get; set; }
        public int SkillID { get; set; }
        public string SkillName { get; set; }
        public DateTime SkillValidTo { get; set; }
    }

    public class Persons
    {
        public int PerID { get; set; }
        public string PerName { get; set; }
        public List<Skill> PerSkills { get; set; }
    }

this code don't work...

    persons = from per in personen
              join sk in skills on per.PerID = sk.PerID into joinedTable
              select new Persons()
              {
                  PerID = per.PerID,
                  PerName = per.PerName,
                  PerSkills = sk.Where(x => x.PerID == PerID).ToList() 
              } into joinedPersonsSkills
              select joindPersonsSkills;

Don't join at all. It's the *ORM*'s job to create the appropriate SQL statement from the entities' relations. I say *entities* and not tables for a reason. An ORM *maps* tables and their relations to *objects*, it doesn't expose tables as objects. It wouldn't be useful if it didn't map their relations too — Panagiotis Kanavos, Jan 21 '19 at 09:27
There should be a mechanism in your ORM to populate the `PerSkills` automatically without you needing to join it. — bit, Jan 21 '19 at 09:29
You shouldn't have to do anything more than write `myContext.Persons.Where(p=>someCondition).ToList()` to get both persons *and* their skills. If you can't do that, there's something wrong with the context's configuration. — Panagiotis Kanavos, Jan 21 '19 at 09:29

score 1 · Answer 1 · answered Jan 21 '19 at 10:36

I'm not sure whether it is wise to do it like this, let's talk about that later.

You have two tables, Persons and Skills, with a one-to-many relation: every Person has zero or more Skills, every Skill belongs to exactly one Person, namely the Person with the same primary key value as the Skill's foreign key in PerId.

Requirement: give me all Persons, each with their skills

You use a normal join for this. The result might be that you get tuples [Person 1, Skill 1], [Person 1, Skill 2], [Person 1, Skill 3] etc. The values of Person 1 are copied over and over again.

What you want is Person 1 with all his Skills, Person 2 with all his Skills, Person 3 with all his Skills etc. For this you should use GroupJoin

var personsWithTheirSkills = persons.GroupJoin(skills, // GroupJoin persons and skills
    person => person.PerId,           // from every person take the primary key PerId
    skill => skill.PerId,             // from every Skill take the foreign key PerId
    (person, skills) => new           // from every Person with all his matching skills
    {                                 // make one new object containing the following properties
         PerId = person.PerId,
         PerName = person.PerName,
         PerSkills = Skills.ToList(),
    });

Now you will have only one object for person 1, containing one PerId, one PerName, and a list of Skills.

The result will be in an anonymous object. If you need this object outside this procedure you should put the result in a real class object. Simple replace the line with the new into:

(person, skills) => new Person
{
    PerId = person.PerId,
    ...
});

Note: the join is not executed yet, it is still an IEnumerable. To execute the query and materialize the objects, use foreach, ToList, FirstOrDefault etc.

// get Person 4 with all his Skills:
var requestedPerson = personsWithTheirSkills
    .Where(person => person.PerId == 4)
    .FirstOrDefault();

Note: it can be that there is no Person with PerId 4, and even if there is such a Person, he could have no Skills at all!

Improvements

One of the slower parts of a database query is the transport of the selected data from your database management system to your process. Hence it is wise to select only the data you actually plan to use.

In the example above I only used the data of Person 4, yet all data of all Persons was transferred to your process.

Apparently you have classes that performs SQL queries using Dapper. These classes fetch your Persons and your Skills.

If you have to ask regularly for Persons with their Skills, consider creating a function that fetches this for you and let SQL and Dapper perform the GroupJoin. You could order the programmer of your Dapper queries to create a function for this (or do it yourself). An example of how to ask for items with their many subitems can be found here on stackoverflow. I have the impression that newer versions of Dapper have this functionality implemented.

score 0 · Answer 2 · answered Jan 21 '19 at 09:48

Try this:

persons = from per in personen
              join sk in skills on per.PerID equals sk.PerID
              select new Persons()
              {
                  PerID = per.PerID,
                  PerName = per.PerName,
                  PerSkills = per.Skills 
              } into joinedPersonsSkills
              select joindPersonsSkills;

var result = persons.Where(x=>x.PerID==PerID).ToList();

score 0 · Answer 3 · answered Jan 22 '19 at 12:09

Thanks for help.

Persons and skills are an simple example for me to anderstand the solution.

My problem is, i get over 2.000 datasets to List in 200ms. If i join in sql the count(skills) to it, the query is about 14 seconds.

If i use dapper to join it into list-objects for each person, its about 9 seconds. Perhaps i use version 1.50.2 and can't update to newer because framework.

So i decide to join it with linq to increase performance, now i test it.

Thanks to Harald Coppoolse for the answer!

Join table with linq into object-list in class

3 Answers3

Improvements