How can I join two entities using Linq

Question

I have two models RP and RPHistory as shown below they both contains same data, How can i combine them to create single list data from the two?

 public class RP: 
    {
        public int ID { get; set; }
        public int RPID { get; set; }
        public string Name { get; set; }
        public int ProductID { get; set; }
    }

I have the second modal as

 public class RPHistory: 
    {
        public int ID { get; set; }
        public int RPID { get; set; }
        public string Name { get; set; }
        public int ProductID { get; set; }
    }

I have this code to get data from RP:

var RPs = await Context.RP.Where(b => b.ProductID  == request.ID).ToListAsync();

How can i join update the above code to include RPHistory?

Matt · Accepted Answer · 2020-04-17T18:45:46.347

0

Try something along the lines of:

var RPs = await Context.RP.Where(b => b.ProductID  == request.ID)
                          .Select(x=> new {
                                           x.ID, 
                                           x.RPID, 
                                           x.Name, 
                                           x.ProductID, 
                                           History = Context.RPHistory
                                                            .Where(y=> y.RPID == x.RPID)
                                                            .ToList() //Note nested Select 
                                                                      //assuming List and 
                                                                      //there can be more 
                                                                      //than one entry
                                           }
                                  ).ToListAsync();

EDIT:

If you want a concrete class just do:

public class RpWithHistory {
  public int ID;
  public int RPID;
  public string Name;
  public int ProductID;
  public List<RPHistory> History;
}

// Then in your code:

var RPs = await Context.RP.Where(b => b.ProductID  == request.ID)
                              .Select(x=> new RpWithHistory {
                                               ID = x.ID, 
                                               RPID = x.RPID, 
                                               Name = x.Name, 
                                               ProductID = x.ProductID, 
                                               History = Context.RPHistory
                                                                .Where(y=> y.RPID 
                                                                        == x.RPID)
                                                                .ToList()
                                               }
                                      ).ToListAsync();

edited Apr 17 '20 at 18:45

answered Apr 16 '20 at 22:01

Matt

25,943
66
198
303

Matt, Thank you! I used Mapping using Automapper as var rpDTOs = Mapper.Map>(RPs ); and I get the exception error s Mapping types:List`1 -> IList`1. but this is what i want ` – Rob Apr 16 '20 at 22:44
Matt, Can we cast the nested select to Ilist? – Rob Apr 17 '20 at 05:38
Probably not, due to covariance if I remember my CS correctly. See this https://stackoverflow.com/questions/9005944/convert-listlistobject-to-ilistilistobject. – Matt Apr 17 '20 at 17:20
Matt, Now i know When i get the records from the DB, the select statement is converting it into an anonymous type, which Automapper doesn't know how to convert. Can we fix that? – Rob Apr 17 '20 at 17:45
I don't know much about automapper and that's a different question of which I'm not sure – Matt Apr 17 '20 at 17:48
but is it possible to convert anonymous type to any type? – Rob Apr 17 '20 at 17:50
one more help i get this `RpWithHistory.History is in accessible due to its protection level` – Rob Apr 17 '20 at 18:44
Sorry Matt now i get can not implicitly convert Generic.List RPHistory to ... Generic.List. RPHistory in this line ` History = Context.RPHistory .Where(y=> y.RPID == x.RPID) .ToList()` – Rob Apr 17 '20 at 18:55
I don't know - that's beyond the scope of this question. Honestly, you should try to do what you want to do without AutoMapper first, then get back to it. – Matt Apr 17 '20 at 18:57
I am not using Auto mapper now, i take that part out. Ok Thanks for the help! – Rob Apr 17 '20 at 18:59

How can I join two entities using Linq

1 Answers1