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
– xeraphim, Commented Nov 4, 2013 at 14:37

Community · Accepted Answer · 2017-05-23 12:13:03Z

1

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

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Nov 4, 2013 at 14:32

Lin

15.2k4 gold badges50 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

NuNn DaDdY · Accepted Answer · 2013-11-04 14:27:56Z

0

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

answered Nov 4, 2013 at 14:27

NuNn DaDdY

3,0122 gold badges17 silver badges19 bronze badges

1 Comment

xeraphim Over a year ago

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<int> The DTO looks like this:

public class MyDto     {         public long? Id1 { get; set; }          public long? Id2 { get; set; }          public ICollection<int> PersonIds { get; set; }     }

xeraphim · Accepted Answer · 2013-11-05 07:22:10Z

0

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

answered Nov 5, 2013 at 7:22

xeraphim

4,70311 gold badges64 silver badges119 bronze badges

Collectives™ on Stack Overflow

EntityFramework / LinQ load entity from database to dto

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related