Problem creating an empty IQueryable <T>

Question

Basically, I want to combine two Iqueryable into one Iqueryable, and then return the full set of records after the end of the loop. It works fine, but in the end my objret has nothing, but when I debug the obj loop, there are some entries. wht im do wrong

IQueryable<MediaType> objret = Enumerable.Empty<MediaType>().AsQueryable(); var typ = _db.MediaTypes.Where(e => e.int_MediaTypeId != 1 && e.int_MediaTypeId_FK == null).ToList(); for (int i = 0; i < typ.Count; i++) { IQueryable<MediaType> obj = _db.MediaTypes.Where(e => e.bit_IsActive == true && e.int_MediaTypeId_FK == typ[i].int_MediaTypeId); IQueryable<MediaType> obj1 = _db.MediaTypes.Where(e => e.int_OrganizationId == Authorization.OrganizationID && e.bit_IsActive == true && e.int_MediaTypeId_FK == typ[i].int_MediaTypeId); if (obj1.Count() > 0) obj.Concat(obj1); if(obj.Count() > 0) objret.Concat(obj); } return objret; 

Answer 1

Like other query operators, Concat does not modify an existing sequence - it returns a new sequence.

So these lines:

 if (obj1.Count() > 0) obj.Concat(obj1); if(obj.Count() > 0) objret.Concat(obj); 

it should be

 if (obj1.Count() > 0) objret = objret.Concat(obj1); if(obj.Count() > 0) objret = objret.Concat(obj); 

I'm not sure how well IQueryable will handle this, given that you mix LINQ with SQL (possibly Entities) with Enumerable.AsQueryable , mind you. Given that you are already executing queries to some extent due to Count() calls, do you think that instead of List<T> you need to create a List<T> ?

(You don’t have to do Count() - just call List<T>.AddRange(obj1) and ditto for obj .)

As Jerehan noted, ideally, it would be nice to use a solution that could do everything so that the database does not loop at all on your C # code.

Answer 2

I think you should not do this with a for loop. The code you sent will be sent twice in db for each active media type to get Count () and additionally twice to get the actual results.

Checking the Count () property is not required: merging empty result sets has no additional effect.

In addition, I think that what you are trying to achieve can be accomplished with a single request, something like (not verified):

  // build up the query var rootTypes = _db.MediaTypes.Where(e => e.int_MediaTypeId != 1 && e.int_MediaTypeId_FK == null); var activeChildren = _db.MediaTypes .Where(e => e.bit_IsActive); var activeChildrenForOrganization = _db.MediaTypes .Where(e => e.int_OrganizationId == Authorization.OrganizationID && e.bit_IsActive); var q = from types in rootTypes join e in activeChildren on types.int_MediaTypeId equals e.int_MediaTypeId_FK into joined1 join e in activeChildrenForOrganization on types.int_MediaTypeId equals e.int_MediaTypeId_FK into joined2 select new {types, joined1, joined2}; // evaluate the query and concatenate the results. // This will only go to the db once return q.ToList().SelectMany(x => x.joined1.Concat(x.joined2));