Performing join using linq query in Entity Framework

Question

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 :)

score 1 · Accepted Answer · answered Apr 11 '17 at 16:46

1

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();

answered Apr 11 '17 at 16:46

Sergey Berezovskiy

232,247
41
429
459

1

Thanks a lot! It seems to work, but I do get some exception telling me that I can't convert from "type System.Nullable to target type 'int'", but seems like I can work around that by adding " ?? default(int) " to the places that it gives me that exception :) – sunero4 Apr 11 '17 at 16:53
1

@sunero4 that means you have nullable fields in database which are not nullable in your dto. You can use default values if it meets your needs – Sergey Berezovskiy Apr 11 '17 at 16:55
Oh, so it might be better to just allow them to be nullable in the DTO? – sunero4 Apr 11 '17 at 16:57
1

@sunero4 that would be perfect solution, because having `0` value is not same as not having value at all – Sergey Berezovskiy Apr 11 '17 at 16:58
Great, I'll do that then :) Thanks a lot for your help! – sunero4 Apr 11 '17 at 16:58

Performing join using linq query in Entity Framework

1 Answers1