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'

Answer 1

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

Answer 2

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

Answer 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;

Answer 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();

Answer 5

Try adding .ToList();

var listOppLineData = (from o in db.opportunity_vws
                                   where o.fiscal_yr_and_qtr == qtr
                                   select o
                                  ).ToList();

Update

And fix the query.

You will not have anything to iterate over without the .ToList();

Head Out of Butt Update

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

Put your 'short' before the OR, so then the second part is never evaled if not needed. I would still use the .ToList() though.

Answer 6

You can use declaration like this:

var listOppLineData = (object)null;

Then use that variable as:

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