EntityFramework / LinQ load entity from database to dto

Question

I have a problem loading the correct data to a DTO using EF and linq.

From my DB I receive following example data:

1, 1, 1

1, 1, 2

1, 1, 3

2, 1, 4

2, 1, 5

etc.

I want to load these data in a DTO which should look like this:

int, int, ICollection<int>

so for the example data:

new MyDto(1, 1, new List<int> { 1, 2, 3 });
new MyDto(2, 1, new List<int> { 4, 5 });

This is my linq query

var result = (from adresses in context.Adress
              join person in context.Person on adresses.PersonId equals person.Id
              select new MyObj { Id1 = adresses.Id1, Id2 = adresses.Id2, PersonId = person.Id })

But it is wrong, since it doesn't group by Id1 and Id2 and doesn't put the personIds in the list...

Could you please tell me how I can achieve this?

Hi Steven, thanks for your post. I thought that it could be achieved with `group by`, but I couldn't get it to work. I have never really used linq before, so if you could give me a hint how to do it I would be very thankful — xeraphim, Nov 04 '13 at 14:37

score 1 · Answer 1 · edited May 23 '17 at 12:13

Pivot data using Linq is a better way. You can take look at this link:

Is it possible to Pivot data using LINQ

To answer your question, below is an example:

var result = (from adresses in context.Adress
              join person in context.Person on adresses.PersonId equals person.Id
              group address by address.Id1 into gResult
              select new{
                Id1 = gResult.Key,
                Id2 = gResult.Select(r => r.Id2).FirstOrDefault (),
                Id3 = gResult.Select (r => r.Id3)
              });

score 0 · Answer 2 · answered Nov 04 '13 at 14:27

In your Address class, do you have a property for a Person instance so you're able to set up a relationship between the two classes? If so, the following query may get you the result set that you're looking for:

public class Address 
{
   public int Id1 { get; set; }
   public int Id2 { get; set; }
   public virtual Person Person { get; set; }
}

public void Foo() 
{
   IEnumerable<MyObj> = context.Address.Select(x => new {
                                                           Id1 = x.Id1,
                                                           Id2 = x.Id2,
                                                           PersonId = x.Person.Id 
                                                         });
}

Hey NuNn DaDdY thank you for your answer, but I only have a `int PersonId` field on the address. I think your answer would work, if PersonId of the DTO would be a `int` but as I wrote in my question, it's a `ICollection` The DTO looks like this: `public class MyDto { public long? Id1 { get; set; } public long? Id2 { get; set; } public ICollection PersonIds { get; set; } }` — xeraphim, Nov 04 '13 at 14:34

score 0 · Accepted Answer · answered Nov 05 '13 at 07:22

Thanks for the good answers of you guys, I could finally work it out :-)

var result = from tuple in (from address in context.Adresses
                            join person in context.Persons on address.PersonId equals person.Id
             select new { person.Id, address.Id1, address.Id2})
             group tuple by new { tuple.Id1, tuple.Id2 } into myGrouping
             select 
                 new MyObj 
                     { 
                         Id1 = myGrouping.Key.Id1,
                         Id2 = myGrouping.Key.Id2,
                         PersonIds = myGrouping.Select(x => x.PersonId).Distinct()
                     };

EntityFramework / LinQ load entity from database to dto

3 Answers3