Casting LINQ expression throws "Internal .NET Framework Data Provider error 1025."

Question

The following code:

model.Items = dbQuery.Select(c => new Item {
    Total = c.Items.Count((Func<Item, bool>)(t => t.Amount.HasValue))
}).ToArray();

Throws the following exception:

Internal .NET Framework Data Provider error 1025.

However the exact code minus the cast works:

model.Items = dbQuery.Select(c => new Item {
    Total = c.Items.Count(t => t.Amount.HasValue)
}).ToArray();

Note: dbQuery is just an EF ObjectQuery.

The expected type for Count() is Func<Item, bool>, and it's even verified by ReSharper that they're the same type by the fact that it grays the cast out, suggesting that it's not needed and they're functionally equivalent.

However, the exception proves that they are not, in fact, functionally equivalent.

Also, I did see this related question/answer, but Count() (in this case, anyway) will not accept an Expression<Func<Item bool>>, so that doesn't work for me.

So why are you trying to add something that is designed to be useless, but that is resulting in your code not working at all. If it hurts when you do that, don't do that. — Servy, Jul 15 '14 at 18:31
@Servy I was trying to abstract the actual issue from the details in the interest of simplicity. But it boils down to the fact that I'm trying to reuse the logic in my (more complicated) expression, and storing this logic in a `Func` isn't working. But if I can get the above code to work, then I can get my more complicated actual problem solved. — Jerad Rose, Jul 15 '14 at 18:46
@JeradRose No, you can't. The code that you've provided is something that could in theory work if the query provider were robust enough to expect it. Actually pulling the code out of the expression and storing it in a delegate actually removes the information from the expression, making it *impossible* for the query provider to access the information no matter how robustly its written. Of course, that's not to say that it's impossible to separate an expression into multiple expressions, because it is, you simply can't do it by putting the code in a delegate. — Servy, Jul 15 '14 at 18:57

Ocelot20 · Accepted Answer · 2014-07-15T19:17:51.413

11

You want to "store [the] expression in a variable, for reuse purposes, and pass that into Count()". This is 100% possible if the Expression can be translated to SQL. The problem here is that you're introducing something that can't be translated to SQL. The particular issue in this case is that the cast gets translated to an Expression.Convert. Your types definitely aren't represented in SQL, so this won't be able to execute. Plug both into LINQPad and check out the IL to see the difference.

As the other related question/answer you linked to suggests, you need to pass your predicate as an Expression rather than a Func (how to do the work vs. pointing to a location in code that does the work). The problem, as you noted, is that the Count method doesn't take an Expression, but this is easily resolved by using AsQueryable() which will give you the IQueryable extension methods that take Expression.

This should have no problems executing:

Expression<Func<Item,bool>> predicate = i => i.Amount.HasValue;

model.Items = dbQuery.Select(c => new Item {
    Total = c.Items.AsQueryable().Count(predicate)
}).ToArray();

edited Jul 15 '14 at 19:17

answered Jul 15 '14 at 19:10

Ocelot20

10,510
11
55
96

1

Pretty sure the query provider won't know what to do with the `AsQueryable` call. – Servy Jul 15 '14 at 19:13
I didn't think it would either, but it does (in EF6 -> MSSQL2008 at least). Credit to the commenter on the accepted answer of the question OP linked to. – Ocelot20 Jul 15 '14 at 19:15
And it generates the appropriate SQL code, rather than just doing the work on the application side? – Servy Jul 15 '14 at 19:16
Yes, confirmed that as well. – Ocelot20 Jul 15 '14 at 19:17

score 1 · Answer 2 · answered Jul 15 '14 at 18:47

1

The problem is that the data provider is not able to turn that LINQ expression into a SQL statement. In the one that is not working, Count is being passed a delegate to a method. Think of it as passing a pointer to a method like:

private static bool MyFunction(Item item)
{
    return item.Amount.HasValue; 
}

Now, the problem is that data provider is not going to get into that method an try to figure out how to turn it into SQL.

The data provider is very capable of traversing expression trees to build up SQL, which is how the "minus cast" example works, and also the related answer linked in your post.

If you can update your post with what you are trying to accomplish, I may be able to help you out.

answered Jul 15 '14 at 18:47

Mike Hixson

5,071
1
19
24

Great explanation, thanks. You may have answered my question: it's not possible. Basically I'm trying to do what you're saying I can't do: store my expression in a variable, for reuse purposes, and pass that into `Count()`. – Jerad Rose Jul 15 '14 at 18:50
Actually, it *is* an expression. It's an expression that represents the creation of a delegate, and as a result all of the information *is* in the expression and it *can* be translated to SQL, in theory. The query provider simply doesn't expect this particular operation, and so it errors out, but that's because they didn't expect it and code around it, not because the information they need isn't there. – Servy Jul 15 '14 at 18:50
@JeradRose Then you're doing something that's not shown in your question, because in your question that's not at all what you're doing. – Servy Jul 15 '14 at 18:51
@Servy I see your point, but whether I pull the expression out into a variable or simply cast the expression inline, I get the same exception. Are you sure what Mike is suggesting is not correct? In theory, what you say makes sense (that's what I thought too). But in practice, it looks like .NET is somehow treating the cast differently from the un-casted expression. – Jerad Rose Jul 15 '14 at 19:01
Is there any pattern to the expressions you want to store? Like for example, all expressions will evaluate if some property (determined at runtime) is nullable? – Mike Hixson Jul 15 '14 at 19:05
@JeradRose The fact that you get the same exception isn't really relevant. In one case the query provider could solve the problem, but chooses not to, and in another it's an impossible problem that it cannot solve no matter what it does. That the error message is the same doesn't change the fact that the problem is entirely different in those two cases. – Servy Jul 15 '14 at 19:07
@Servy the Func expresion itself can not be used to generate sql. I suppose it would be possible to get into the IL of the anonymous method and figue it out that way, but that wouldn't bee something provided by a general purpose data provider. Have a look at http://stackoverflow.com/questions/793571/why-would-you-use-expressionfunct-rather-than-funct – Mike Hixson Jul 15 '14 at 19:12
@MikeHixson That's true if you have a separate `Func` variable outside of the expression that you reference inside of the question. When the `Func`'s value is defined in the expression itself, as is the case in the question, then the expression itself doesn't just have the function's IL to work with, it has an actual expression representing the C# code used to create the `Func`, which the query provider is entirely capable of translating into SQL code without touching any IL code. It is the act of pulling the code out of the `Expression` and putting it into a variable that changes things. – Servy Jul 15 '14 at 19:15

Casting LINQ expression throws "Internal .NET Framework Data Provider error 1025."

2 Answers2

Linked