CODEWITHSUNDEEP
c#entity-frameworklinq
sunero4
sunero4

Reputation: 840

Performing join using linq query in Entity Framework

I'm trying to select some data from a table in my database using a join in a linq query, but I can't seem to grasp how to save it to the list of DTO's that I would like to return. I've been looking at this post for directions at using the lambda expression: C# Joins/Where with Linq and Lambda but it seems like that guy is trying to accomplish something slightly different than me; I want to compare the value CPR (from the table Coworkers) and the value CPR (From the table Duties) and select all of those where the Projektname (from the table Duties) are equal to the string projektname.

What I've written so far of the method is this:

    public List<CoworkerDTO> GetCoworkers(string projektname)
    {
        _coworkerlist = new List<CoworkerDTO>();

        using (var context = new F17ST2ITS2201608275Entities())
        {
            var dataset =
                from co in context.Coworkers
                join du in context.Duties on co.CPR equals du.CPR
                where du.Projektname == projektname
                select new {Coworkers = co};

            foreach (var element in dataset.ToList())
            {
                _coworkerlist.Add(element);
            }
        }
        return _coworkerlist;
    }

The CoworkerDTO looks like this:

class CoWorkerDTO
{
    public string Fornavn { get; set; }
    public string Efternavn { get; set; }
    public int Alder { get; set; }
    public string CPR { get; set; }
    public decimal AntalTimer { get; set; }
}

The table Coworkers has a column that corresponds to each of the properties above, so I guess my question is how to somehow convert the selection that I get into a list of the CoworkerDTOs. Sorry for the long post, and if my english is a bit confusing, as it's not my first language. Thanks in advance :)

Upvotes: 1

Views: 3350

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236328

You should convert Coworkers entity into CoWorkerDTO. You can do it manually (assume properties have same names and types):

    var dtos =
        from co in context.Coworkers
        join du in context.Duties on co.CPR equals du.CPR
        where du.Projektname == projektname
        select new CoWorkerDTO {
           Fornavn = co.Fornavn,
           Efternavn = co.Efternavn,
           Alder = co.Alder,
           CPR = co.CPR,
           AntalTimer = co.AntalTimer
        };

    return dtos.ToList();

Or you can use something like AutoMapper Queryable Extensions to do that projection automatically:

Mapper.Initialize(cfg => 
    cfg.CreateMap<Coworkers, CoWorkerDTO>());

And query with projection will look like

 var entities =
        from co in context.Coworkers
        join du in context.Duties on co.CPR equals du.CPR
        where du.Projektname == projektname
        select co;

 return entities.ProjectTo<CoWorkerDTO>().ToList();

Upvotes: 2

Related Questions