Write a translatable method for LINQ TO Entities

Question

Using Entity Framework (LINQ to Entities)

The following is working just fine. The expressions got translated to SQL

var foos = ctx.Foos.Select(f => new {
   P1 = ctx.Bars.FirstOrDefault(b => b.SomeProp == "Const1" && f.X1 == b.Y),
   P2 = ctx.Bars.FirstOrDefault(b => b.SomeProp == "Const2" && f.X2 == b.Y),
   P3 = ctx.Bars.FirstOrDefault(b => b.SomeProp == "Const3" && f.X3 == b.Y),
}

The repetitive expression b.SomeProp == "..." && f.X* == b.Y is actually a simplified version of the real expression, but if you can help me wit this. I'll figure out the rest as well...

What I would like to write is something like this. (Preferred)

var foos = ctx.Foos.Select(f => new {
   P1 = f.GetBar("Const1", f.X1),
   P2 = f.GetBar("Const2", f.X2),
   P3 = f.GetBar("Const3", f.X3),
}

But I might also be fine with something like

P1 = ctx.Bars.GetByFoo(f.X1, "Const1");
- or -
P1 = ctx.Bars.FirstOrDefault(GetByFoo(f.X1, "Const1"))
- or -
P1 = ctx.Bars.GetByFoo(x => x.X1, "Const1");

Based on this answer https://mcmap.net/q/731065/-how-does-one-use-a-custom-property-in-a-linq-to-entities-query The closest I came so far is

ctx.Bars.FirstOrDefault(GetByFoo(x => x.Y == f.X1 , "Const1"))

and

private static Expression<Func<Bar, bool>> GetByFoo(Func<Foo, bool> optionSelector, string par1)
    {
        return b => b.SomeProp == par1 && optionSelector(o);
    }

Unfortunately, this a) Is still far from desired b) this does not work :(. It gives a run-time exception:

variable 'f' of type 'Foo' referenced from scope '', but it is not defined

It is essential that the expression keeps translatable. I am not fine with retrieving all foos and then retrieving the Bar for each foo.

Indene

The problem is that everything is part of the select lambda expression tree, hence is not executable and also non translatable, because the translation is based on knowledge, thus cannot support unknown methods. – Famine

The 1/2/3 fields seems (to me) like you're trying to use it as a FK. If that is the case, then why are you not using EF's default approach to navigational properties? You don't need to manually join the two entities together, EF can do that for you. – Party

@Party They are almost a FK. Bar.Y is not unique, the combination Y and SomeProp however is unique. The properies X1, X2, X3 however do not "know" the SomeProp value, that is why I still need to be provide it. – Indene

Answer 1

how about something along these lines

public static class Extensions
{
    public static IQueryable<T> Filter<T>(this DemoContext instance, Expression<Func<T, bool>> predicate = null, string value = null)
        where T : class, IMarker
    {
        return instance
            .Set<T>()
            .Where(p => p.SomeProp == value)
            .Where(predicate);
    }
}

that should allow you to write something like

var inner1 = ctx.Filter<Bar>(value: "Const1");
var inner2 = ctx.Filter<Bar>(value: "Const2");
var inner3 = ctx.Filter<Bar>(value: "Const3");

var query = from foo in ctx.Foos
            select new
            {
                P1 = inner1.FirstOrDefault(p => p.Y == foo.X1),
                P2 = inner2.FirstOrDefault(p => p.Y == foo.X2),
                P3 = inner3.FirstOrDefault(p => p.Y == foo.X3),
            };

where the IMarker interface is just to help you deal with specialized predicates

public class Foo : IEntity
{
    public string X1 { get; set; }
    public string X2 { get; set; }
    public string X3 { get; set; }

}

public class Bar : IMarker
{
    public string SomeProp { get; set; }
    public string Y { get; set; }
}

public interface IEntity
{
}

public interface IMarker : IEntity
{
    string SomeProp { get; set; }
}

Turbid

Good try, but no. This also gives: LINQ to Entities does not recognize the method '... .Find...' method, and this method cannot be translated into a store expression. – Indene

Indeed, you might want to play though with IQueryable inner queries instead. check my update for an example that isolates query definitions specific to Bar and applies further filtering later on in the query – Turbid

well, inner queries might do it. In the mean time I had rewritten it with query syntax and using "let". What might be te same as using inner queries. I'll give it a try. – Indene