is it possible to use a range variable from a linq (query syntax) as a parameter?

c# entity-framework-6 linq

Question

I'm trying to use a range variable from a linq query as a parameter;

something easy like;

private static Expression<Func<context.Table1, bool>> funstuff(context.Table2 data)
{
    return x => true;
}

using it like, another simple example, cross join;

var fun = from table2 in context.Table2
          from table1 in context.Table1.Where(funstuff(table2))
          select table1;

var f = fun.ToList();

and i'm getting this error;

variable 'table2' of type 'context.Table2' referenced from scope '', but it is not defined

is it possible to use a range variable as a parameter under entity framework 6?

edit

context is the dbcontext of entity framework

this exact code does work;

var fun = from table2 in context.Table2
          from table1 in context.Table1.Where(x => table2.Id == 1)
          select table1;

var f = fun.ToList();

and this doesn't work;

private static Expression<Func<context.Table1, bool>> funstuff(context.Table2 data)
{
    return x => data.Id == 1;
}



var fun = from table2 in context.Table2
          from table1 in context.Table1.Where(funstuff(table2))
          select table1;

var f = fun.ToList();

I was expecting that it would work.

1
1
10/18/2017 11:39:21 AM

Accepted Answer

I assume that your context is a DbContext.

Although most functions are similar, there is a difference between Queryable and Enumerable.

IEnumerable objects contain all code to enumerate over the objects in a collection which is in local memory. IEnumerable obects can use local functions while enumerating over them.

Queryable enumerations are meant to be performed on an other processor. An IQueryable object contains an expression that can be translated to a format that the other processor understands.

This translation is not in the Expression, but in another property of the IQueryable: the Provider. The Provider knows on which type machine the Expression is to be performed. Upon request the Provider will translate (compile) this expression into a format that this machine understands and will send the translation to this machine.

The object context.Table2 implements IQueryable. The Provider in the IQueryable knows how to translate the query into SQL. SQL does not know any of your personal functions like funstuff, and thus the Provider does not know how to translate it into SQL. In fact, a DbSet does not even support all kinds of LINQ methods.

See MSDN: Supported and Unsupported LINQ Methods (LINQ to Entities)

So you can't call any of your local functions as long as your statements are IQueryable.

If it is really important to call the local functions you could perform them as IEnumerable. You do this with the function AsEnumerable. This will bring the data that is input of the AsEnumerable to local memory. After that you can call all local functions as if your query was an IEnumerable

However there is a performance risk: If the input is thousends of objects and in the end you'll only end up with a few objects, it is a waste to bring all this information to local memory.

Conclusion: try to limit the data that is transferred to local memory to about the size you will need in your end result before calling AsEnumerable. After that you can call any local function you want.

2
10/18/2017 7:54:01 AM

Popular Answer

You cannot do it this way. Consider Entity Framework analyzing your LINQ query to convert it to SQL query. It analyzes all the way down to funstuff(table2) call. It sees that it's a method call expression unknown to it (so not something like SqlFunctions.GetDate()). Ok, now EF analyzer needs to call this method to obtain expression. If method had no arguments or its arguments were known - no problem to call it. But this method argument represents query parameter table2. It does not have defined value when analyzing expression. It will only have a value (range of them) during SQL query execution. At that time, on SQL server, your method of course cannot be called with each value from that range. So query analyzer has absolutely no way to convert your query to SQL and fails.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow