Instantiate empty IQueryable for use with Linq to sql

Question

I need to be able to either have an optional parameter in a Linq query, or be able to assign the query to a var in something like an IF if that optional parameter needs to be removed from the query.

If I set the query var inside the IF statement then it tells me that the var doesn't exist in context when I try to loop through it.

if (whichGroup == "All")
        {
            var listOppLineData = (from o in db.opportunity_vws
                                   where o.fiscal_yr_and_qtr == qtr
                                   select o
                                  );
        }
        else
        {
            var listOppLineData = (from o in db.opportunity_vws
                                   where o.fiscal_yr_and_qtr == qtr && o.Group == whichGroup
                                   select o
                                  );
        }

        foreach (var data in listOppLineData)  //listOppLineData doesn't exist here
        {

I need to set the var before the IF statement I think, but I don't know what to set it to.

var listOppLineData = ""; // gives me 
Cannot implicitly convert type 'System.Linq.IQueryable<Forecast.Models.opportunity_vw>' to 'string'

IQueryable listOppLineData = new IQueryable(); //gives me
        Cannot create an instance of the abstract class or interface 'System.Linq.IQueryable'

Solution 1

Try this:

IQueryable<opportunity_vw> listOppLineData;

This is defining the variable, but you are waiting to initialise it.

Also, looking at your query, you could do it all in one, like so:

var listOppLineData = (from o in db.opportunity_vws
                      where o.fiscal_yr_and_qtr == qtr && (o.Group == whichGroup || whichGroup == "All")
                      select o
                      );

Solution 2

Try this. You can create a generic type with T or a specific type by replacing T with your type name.

IQueryable listOppLineData = Enumerable.Empty<T>().AsQueryable()

Solution 3

Your problem is that when you declare a variable in C#, you declare it only in the current scope. So, if you declare the variable listOppLineData in the if block, you can use it only inside that block, but not anywhere else. (You can define another variable with the same name in another block, but it would be a completely different variable, so it wouldn't have the value you assigned to the first variable.)

As you already figured out, you need to declare the variable outside the if blocks.

Your first attempt didn't work, because the compiler saw that you didn't explicitly specify the type of the variable (that's what var does) and you assigned a string to the variable. So it inferred that the type of the variable is string. And you can't assign something that is not a string to such variable.

Your second attempt didn't work, because you can't create an instance of an interface. (If you want to create an instance, it has to be some specific type, usually a class.) And even if you fixed that, the non-generic IQueryable doesn't know the type of items in it, so the foreach wouldn't work well.

But you don't need to assign any value to the variable, since it's assigned in both branches of the if. And the correct type here is the generic IQueryable<T>, with T being opportunity_vw. That means the correct code is:

IQueryable<opportunity_vw> listOppLineData;

if (whichGroup == "All")
{
    listOppLineData = …;
}
else
{
    listOppLineData = …;
}

foreach (var data in listOppLineData)
{
    …
}

If you really wanted to assign some value to the variable (although there is no reason to do that here), you could use null (which represents no value):

IQueryable<opportunity_vw> listOppLineData = null;

Solution 4

For me answer was to set to empty from existing query and be able to call async without exception , if it was transformed to enumerable:

query1 = query1.Take(0);

It was used to union 2 queries from different tables in one common object and one of them could became empty in some validation cases. If I move first query from union to Enumerable I will get exception, if it will be called with async function(ToListAsync()), when called union:

var res = (await query1
    .Select(q1=> new NewClass(q1))
    .ToListAsync())
    .Union(query2
    .Select(q2=> new NewClass(q2)))
    .ToList();

View more solutions

Comments

[svick]

That's nonsense. 1. ToList() won't fix anything here. 2. You certainly can iterate IQueryable<T> without calling ToList() (because it inherits from IEnumerable<T>).

[Trey Gramann]

Good catch. I think I misread this question several times now.

[svick]

BTW, the order of evaluation shouldn't matter here, since the code is actually not executed, it's converted to SQL.

[Trey Gramann]

Put it in LINQPad and see.

[user2073077]

That did the trick!! Thanks a bunch. I wasn't as clear as I should have been in the question either. By whichGroup == ALL I needed to query for whichGroup = anything, but I don't know how to do that in SQL.

[svick]

@user2073077 Simon's code does exactly that. Notice that if whichGroup == "All", then the value of o.Group doesn't actually matter and that part of the condition will be always true. (Though I have no idea if your SQL server is smart enough to realize that.)

[svick]

You mean I should create a database that first this model and then look at the generated SQL?

[RoLYroLLs]

while the accepted answer helps the OP make his code "better", I truly needed to do instantiate an empty IQueryable and not have it set to null. This truly answers the OP's question of "I need to set the var before the IF statement I think, but I don't know what to set it to."

[Gert Arnold]

Please no, it's absolutely no improvement to use object here. Now how to use listOppLineData in the rest of the code?

[Mohsin Pinjari]

you can use it directly with the variable name 'listOppLineData '. I used this a lot in my web APIs projects and did not have issue yet .

[darkenergy]

Why not use this: IQueryable<T> hierarchies = new List<T>().AsQueryable();

[Admin]

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

[ChiefTwoPencils]

I think the question is, how do you use it as anything other than a list of objects? That's less than helpful!