LINQ to Entities projection of a nested list

Question

Assuming these objects ...

class MyClass { int ID {get;set;} string Name {get;set;} List<MyOtherClass> Things {get;set;} } class MyOtherClass { int ID {get;set;} string Value {get;set;} } 

How to execute a LINQ to Entities Query query using a projection as shown below which will give me a list? This works fine with IEnumerable (assuming MyClass.Things is IEnumerable, but I need to use List)

 MyClass myClass = (from MyClassTable mct in this.Context.MyClassTableSet select new MyClass { ID = mct.ID, Name = mct.Name, Things = (from MyOtherClass moc in mct.Stuff where moc.IsActive select new MyOtherClass { ID = moc.ID, Value = moc.Value }).AsEnumerable() }).FirstOrDefault(); 

Thanks in advance for your help!

Answer 1

Not. You must do this part in L2O.

So you can do:

 var q = (from MyClassTable mct in this.Context.MyClassTableSet select new // note anonymous type; important! { ID = mct.ID, Name = mct.Name, Things = (from MyOtherClass moc in mct.Stuff where moc.IsActive select new MyOtherClass { ID = moc.ID, Value = moc.Value } }).AsEnumerable(); MyClass myClass = (from mct in q select new MyClass { ID = mct.ID, Name = mct.Name, Things = mct.Things.ToList() }).FirstOrDefault(); 

It is not possible to do this in a single request.

Answer 2

I'm not sure what exactly you are being asked, but List<T> implements IEnumerable<T> (which is just an interface for an enumerated sequence).

Code that will do your projection and have Things be List instead of IEnumerable will use the ToList () operator, which creates a List<T> from any IEnumerable<T> this:

 MyClass myClass = (from MyClassTable mct in this.Context.MyClassTableSet select new MyClass { ID = mct.ID, Name = mct.Name, Things = (from MyOtherClass moc in mct.Stuff where moc.IsActive select new MyOtherClass { ID = moc.ID, Value = moc.Value }).ToList() }).FirstOrDefault(); 

Answer 3

Take a look at the following solution. You can separate the fields for List and IEnumerable in your class:

 class MyClass { ... List<MyOtherClass> m_Things = new List<MyOtherClass>(); public List<MyOtherClass> Things { get { if (ThingsForLinq != null) { m_Things = ThingsForLinq.ToList(); ThingsForLinq = null; } return m_Things; } set { m_Things = value; } } public IEnumerable<MyOtherClass> ThingsForLinq { get; set; } } 

So you should use ThingsForLinq in your EF Linq queries:

 MyClass myClass = (from MyClassTable mct in this.Context.MyClassTableSet select new MyClass { ID = mct.ID, Name = mct.Name, ThingsForLinq = (from MyOtherClass moc in mct.Stuff where moc.IsActive select new MyOtherClass { ID = moc.ID, Value = moc.Value }).AsEnumerable() }).FirstOrDefault(); 

And use things later:

 myClass.Things.Add(...) 

Answer 4

This answer was helpful, but I do. It converts to POCOs and can support unlimited nested lists. Very simple but powerful:

 Product product = new Product(); List<CoverageCondition> covCondList = null; CoverageCondition covCond = null; Question question = null; List<Question> questList = null; var prod = db.PRODUCTs.Include("COVERAGE_CONDITION.QUESTIONs").Where(p => p.PRODUCT_CODE == productCode).FirstOrDefault(); product.ProductId = prod.PRODUCT_ID; product.ProductCode = prod.PRODUCT_CODE; product.ProductName = prod.PRODUCT_NAME; // go through coverage conditions covCondList = new List<CoverageCondition>(); product.CoverageConditions = covCondList; foreach (COVERAGE_CONDITION cc in prod.COVERAGE_CONDITION) { covCond = new CoverageCondition(); covCond.ConditionId = cc.COV_CONDITION_ID; covCond.ConditionCode = cc.COV_CONDITION_CODE; covCond.ConditionName = cc.COV_CONDITION_NAME; covCondList.Add(covCond); // go through questions for each coverage condtion, if any questList = new List<Question>(); covCond.Questions = questList; foreach (QUESTION q in cc.QUESTIONs) { question = new Question(); question.QuestionId = q.QUESTION_ID; question.QuestionCode = q.QUESTION_CODE; question.QuestionText = q.QUESTION_TEXT; questList.Add(question); } }