Passing query data from LINQ to method in same query

Question

I was able to create a LINQ statement that I thought was strange and wanted to see if anyone else had experience with it.

I've simplified it to this:

var x = db.Test
    .Where(a => a.Field1 == Utils.CreateHash(Preferences.getValue(a.Field2)))
    .FirstOrDefault();

Now how does this translate to database code? Wouldn't LINQ need to do a double query for every single row, i.e. for row a:

1) Query a.Field2
2) Return value to run Utils.CreateHash(Preferences.getValue(a.Field2))
3) Take that value from step 2 and compare it against a.Field1
4) Repeat 1-3 until I've gone through all the rows or returned a matching row

Wouldn't this be extremely inefficient? Or is LINQ smart enough to run this in a better way? Note, I haven't actually run this code so another possibility is a runtime error. Why wouldn't LINQ be smart enough to detect a conflict then and not let me compile it?

I don't think your query will work as is seeing that you have a call to `Utils.CreateHash` in your lambda that you are trying to execute on the DB — BrokenGlass, Mar 30 '12 at 18:52
@BrokenGlass I haven't actually ran it, but then why does it compile? Is Linq not smart enough to know the function is not on the database? — onit, Mar 30 '12 at 18:54
No, the Linq IQuerable provider will fail at runtime, the lambda syntax is valid and would with with Linq to Objects, just not on your provider (which is converting the query into a DB query at runtime) — BrokenGlass, Mar 30 '12 at 18:55

score 2 · Accepted Answer · answered Mar 30 '12 at 19:00

2

The query as is will not work since have a call to Utils.CreateHash in your lambda that you are trying to execute on the DB - in that context you cannot execute that method since there simply is no equivalent on the DB side hence the query will fail.

In general the ability of 3rd party Linq IQuerable providers (e.g. Linq to SQL, Linq to Entities) to access in memory constructs such as methods or classes is very limited, as a rule of thumb at most accessing primitive values or collections of primitives will work.

answered Mar 30 '12 at 19:00

BrokenGlass

158,293
28
286
335

Interesting, but the query works if I passed it an already defined value instead of a.Field2. In fact, it has been working that way for awhile. So what you are saying is that if it cannot evaluate the function before executing the DB side, it will then try and find an equivalent on the DB side and throw a runtime error if none exists? – onit Mar 30 '12 at 19:03
@onit: Yes, exactly - there is no going back and forth - look at it as a one-way street. At best you can pass in pre-computed values, do all the work on the DB, then use `AsEnumerable()` to do some post-processing in memory, but there's no passing of e.g. columns back to your code in the middle of querying the DB – BrokenGlass Mar 30 '12 at 19:05
You are correct. I tried to run it and I got a NotSupportedException. Thanks for the info. Do you have any reference on how Linq will search for a stored proc on the DB side? Or why it can't recognize that it isn't valid. If you don't its ok, I realize I'm getting a bit specific. – onit Mar 30 '12 at 19:09
It can't recognize that this is not valid because your Linq query expression will be translated into a SQL query *at runtime* and not compile-time - that's why you get a runtime error. A workaround could be to write a CLR stored procedure and call that one to do the work. – BrokenGlass Mar 30 '12 at 19:13
I'm very new to Linq and this was very informative. I was shocked that I could even compile such a statement, but it makes more sense if it actually converts it to SQL at runtime. – onit Mar 30 '12 at 19:16

score 1 · Answer 2 · edited May 23 '17 at 12:27

Just to add fast...
A good example to know how this works would be to write (extreme case I agree, but best :) or go through the source code for a custom (open source) LINQ provider (e.g. http://relinq.codeplex.com/ has one etc.).
Basically (I'm simplifying things here a bit), a LINQ provider can only 'map' to Db (supported SQL, functions) what he 'knows' about.
i.e. it has a standard set it can work with, other than that, and with your custom methods (that do not translate to constants etc.) in the frame, there is no way to resolve that on the 'Db/SQL side'.
E.g. with your 'custom' linq provider (not the case here) you could add a specific extension call e.g. .MyCalc() - which would be properly resolved and translated into SQL equivalent - and then you'd be able to use it.
Other than that, I think if I recall correct, provider will leave that as an expression, to resolve when it returns from the Db 'fetch', query operation. Or complain about it in certain cases.
Linq is based on IQueryable - and you can take a look at extension methods provided there for SQL equivalents supported.
hope this helps
EDIT: whether things 'work' or not doesn't matter - it still doesn't mean it'd execute on the Db context - i.e. it'd be unacceptable performance wise in most cases. IQueryable works with expressions (and if you look at the interface) - and linq is executed when you invoke or enumerate usually. At that point some of the expressions may evaluate to a const value that can be worked into a SQL, but not in your case.
Best way to test is to test back the SQL generated by query (possibly this one I think Translate LINQ to sql statement).

score 0 · Answer 3 · answered Mar 30 '12 at 18:50

0

No.

The LINQ provider will run a single SELECT query that selects both fields, then execute your lambda expression with the two values for each returned row.

answered Mar 30 '12 at 18:50

SLaks

868,454
176
1,908
1,964

I'm not sure if this answers my question. Does it return the two fields for all the rows? Or have to query it on a row by row basis and evaluate the lambda expression? If it does it on a row by row basis it seems extremely inefficient to me. Then also if it finds a match from the two fields wouldn't it have to query the db again for all the fields in that row? – onit Mar 30 '12 at 18:53
@onit: As I said in my answer, it will run a single SELECT and iterate the rows in a DataReader until it finds a match. Of course, this depends on the LINQ provider. – SLaks Mar 30 '12 at 19:25

Passing query data from LINQ to method in same query

3 Answers3