Seeking confirmation that you can't replicate linq queries in dbfunction

[julielerman]

I just want to confirm that my findings are correct. It is not an issue.

If I have a scalar funciton that performs a calculation ala

CREATE FUNCTION [DaysInBattle] 
(
	@startdate date,
	@enddate date
)
RETURNS int
AS
BEGIN
	DECLARE @days int;
	select @days=datediff(D,@startdate,@enddate)+1 
   RETURN @days
END

I can replicate that logic inside a mapping which will then be used by inmemory provider.

[DbFunction(Schema = "dbo")]
       public static int DaysInBattle(DateTime start, DateTime end)
       {
           return (int)end.Subtract(start).TotalDays + 1;
       }

(hat tip to @bricelam for explaining that to me).

However if I have a function that does a query, e.g. takes in an id, queries the database, returns a scalar), there is no way to replicate that as a linq query in a mapped dbfunction, correct? At least now with the static requirement. I could test the theory out on the 2.1 preview but thought I'd just ask and not waste time if the easy answer is "nope". I just wanted to be sure with 2.0.2 that I wasn't missing something.

Thanks

[pmiddleton]

@julielerman

I'm not quite following what you want to do exactly. Do you want to run a query vs a different linq provider when the method executes for the in-memory provider?

That you won't be able to do. There is a re-entrance issue which currently blocks nesting calls like that.

[julielerman]

Thanks. yeah I even tried it with 2.1 preview. It's not logical that it would work but I don't want to put my own limitations onto your cleverness so I thought I would try. :) It's mostly so that I can state that it's not possible but I didn't want to make that statement based on an educated guess. Thanks...and closing

[pmiddleton]

We actually tried this last year. See @ajcvickers comment on Sep 15, 2017 over on #7375

It might work in the future if PR #8865 merged in but that is currently blocked by #8864