Kiran Desai Kiran Desai - 1 month ago 9
C# Question

Calling 'Read' when the data reader is closed is not a valid operation. in Entity Framework

I am using Entity Framework in windows application C#, Trying to retrieve data from two entities using DbContext and want to make simple join, but my code breakdown (at var modellst line). My sample code is as below

using (var ctx = new DbEntities())
{

var lst = ctx.AUMaterials.Where(o => o.ServiceRequestTypeId == serviceReqId && o.SSStock.Quantity > 0).ToList();
var modellst = ctx.AUModelMaterials.Where(o => o.ModelId == modelId).ToList();

// here i want to make join on these two list
}


Here in first list thousands of records in AUMaterials entity. And I think it will take to much time to load. Same way in AUModelMaterials entity, Here also thousands of records. But same code works fine in earlier stage.

Answer
 var results = (from t1 in context.AUMaterials
                      join t2 in context.AUModelMaterials
                      on  t1.Col1 equals t2.Col1
                      where t1.ServiceRequestTypeId == serviceReqId && t1.SSStock.Quantity > 0 && t2.ModelId == modelId
                      select new { t1, t2}).ToList();  

If multiple column join

 var results = (from t1 in context.AUMaterials
                      join t2 in context.AUModelMaterials
                      on new {t1.Col1, t1.Col2, t1.Col3 } equals
                          new { t2.Col1, t2.Col2, t2.Col3 }
                      where t1.ServiceRequestTypeId == serviceReqId && t1.SSStock.Quantity > 0 && t2.ModelId == modelId
                      select new { t1, t2}).ToList();