CODEWITHSUNDEEP
c#databaselinqentity-framework
xeraphim
xeraphim

Reputation: 4645

EntityFramework / LinQ load entity from database to dto

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?

Upvotes: 0

Views: 636

Answers (3)

xeraphim
xeraphim

Reputation: 4645

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

Upvotes: 0

Lin
Lin

Reputation: 15188

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

Upvotes: 1

NuNn DaDdY
NuNn DaDdY

Reputation: 2992

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

Upvotes: 0

Related Questions