Linq query to store one to many collection in List object

Question

I have two tables (Application and Hazard) with a one to many relationship.

I want to store the list of Hazards to a List object in my ViewModel.

applicationVm (greatly simplified to just show the collection):

 ...
 public List<Hazard> Hazards { get; set; }

LINQ query (greatly simplified to just show the collection):

  IQueryable<ApplicationVm> applVms;
  applVms = from app in _db.Applications
    ...
    join hz in _db.Hazards on app.Id equals hz.ApplicationId into hzr
      from hzrd in hzr.DefaultIfEmpty()
    select new ApplicationVm { ..., Hazards = hzrd };

Intellisense shows the error 'cannot covert source type Hazard to target type List<Hazard>' on the Hazards = hzrd in the select.

How do I write this LINQ query.

PS I do not want to return applVms as a list since I'm lazy loading.

Hi, this might help you. http://stackoverflow.com/questions/958949/difference-between-select-and-selectmany — Dr. Stitch, Jan 20 '16 at 04:28
@Dr. Stitch As I said in the OP my code samples have been simplified, I have dozens of properties in the ViewModel that are being populated in the LINQ query so selectmany is not at all appropriate. I couldn't even use it if I tried since it is not a recognized symbol in this syntax. — Joe, Jan 20 '16 at 04:43
Any way i hope this works: applVms = from app in _db.Applications select new ApplicationVm { ..., Hazards = (from h in _db.Hazards where h.ApplicationId = app.Id select h) }; I haven't tried it before. :P — Dr. Stitch, Jan 20 '16 at 04:47
You need to grouping like new{hzrd} by new{app.Id }, can refer here http://stackoverflow.com/questions/12133473/simple-linq-query-using-group-in-order-to-return-a-one-to-many-relationship — Anil, Jan 20 '16 at 05:04

Dr. Stitch · Accepted Answer · 2016-01-20T05:12:37.237

I tried it and it works.

class Program
    {
        static void Main(string[] args)
        {
            List<Application> application = new List<Application>();
            List<Hazard> hazard = new List<Hazard>();
            int appID = 1;
            int hazID = 1;
            for (int i = 0; i < 10; i++)
            {
                application.Add(new Application() { AppID = appID, AppName = string.Format("AppName{0}", i + 1) });
                hazard.Add(new Hazard() { HazID = hazID, AppID = appID, HazName = string.Format("HazName{0}", hazID) });
                hazID++;
                hazard.Add(new Hazard() { HazID = hazID, AppID = appID, HazName = string.Format("HazName{0}", hazID) });
                hazID++;
                appID++;
            }

            IEnumerable<AppHaz> appHaz = from app in application
                                   select new AppHaz { AppID = app.AppID, Hazards = (from haz in hazard where haz.AppID == app.AppID select haz).ToList() };

        }
    }

    class Application
    {
        public int AppID { get; set; }
        public string AppName { get; set; }
    }

    class Hazard
    {
        public int HazID { get; set; }
        public int AppID { get; set; }
        public string HazName { get; set; }
    }

    class AppHaz
    {
        public int AppID { get; set; }
        public List<Hazard> Hazards { get; set; }
    }

please refer last line : PS I do not want to return applVms as a list since I'm lazy loading. — Anil, Jan 20 '16 at 05:08
This works and it preserves the Left Outer Join even without the `.DefaultIfEmpty()`. I'll have to test to see if using `.ToList()` in the sub-query undoes lazy loading. — Joe, Jan 20 '16 at 16:46

score 1 · Answer 2 · answered Jan 20 '16 at 10:27

1

Here

from hzrd in hzr.DefaultIfEmpty()
select new ApplicationVm { ..., Hazards = hzrd };

The first line basically flattens the result of the group join, thus hzrd is of type Hazard.

To get the desired result, remove the first line and change the second like this

select new ApplicationVm { ..., Hazards = hzr.ToLIst() };

answered Jan 20 '16 at 10:27

Ivan Stoev

195,425
15
312
343

This works also and also preserves the Left Outer Join even without the `.DefaultIfEmpty()`. – Joe Jan 20 '16 at 16:48

score 0 · Answer 3 · edited Jan 20 '16 at 05:44

0

Why dont you use navigation property app.Hazards?

Anyway, this should work:

_db.Applications.GroupJoin(_db.Hazards, a=>a.Id, h=>h.ApplicationId,(a,hzds)=> new ApplicationVm {...,Hazards = hzds,...})

Also maybe your List should be IEnumerable (sorry I'm out of the computer so I couldn't test it)

edited Jan 20 '16 at 05:44

Anirudh Sharma

7,968
13
40
42

answered Jan 20 '16 at 05:21

tede24

2,304
11
14

it seems that he doesn't want to used LINQ method syntax. – Dr. Stitch Jan 20 '16 at 05:27

Linq query to store one to many collection in List object

3 Answers3