Reputation: 840
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
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