Problem creating empty IQueryable<T> object

Question

Basically i want to merge two Iqueryable to one Iqueryable and then return the complete record set after my loop ends. It runs perfectly but in the end my objret have nothing but when i debug the loop obj have some records. wht im doing 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

Just like the other query operators, Concat doesn't change the existing sequence - it returns a new sequence.

So these lines:

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

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 is going to handle this, given that you're mixing LINQ to SQL (? maybe Entities) with Enumerable.AsQueryable, mind you. Given that you're already executing the queries to some extent due to the Count() calls, have you considered building up a List<T> instead?

(You don't need to execute the Count() at all - just call List<T>.AddRange(obj1) and ditto for obj.)

As jeroenh mentioned, ideally it would be nice to use a solution which could do it all that the database without looping at all in your C# code.

Answer 2

I think you should not do this with a for loop. The code you posted will go to the db for every single active mediatype twice to get Count() and additionally, twice to get actual results.

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

Furthermore, I think what you're trying to achieve can be done with a single query, something along the lines of (not tested):

        // 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));