Mixed portion offline expressions

[Flithor]

If the query selectors contain some complex expressions or method calls using multiple fields but cannot translated to sql , it is often necessary to first query the required fields and use AsEnumerable into offline expressions for calculation. I hope there will be easier-to-use mix offline expression in online expreesion support.

In particular, some dialect EF libraries may not support the translation of some expressions. This feature can provide broader compatibility support.

For example:

//current version code:
return dbContext.MyTable
.Select(t => new
{
    t.Prop1,
    t.Prop2,
    //... many properties
    t.Prop99
})
.AsEnumerable()
.Select(t => new MyOutputClass
{
    OutputProp1 = t.Prop1,
    OutputProp2 = LocalMethod1(t.Prop2, t.Prop3, t.Prop4),
    OutputProp3 = LocalMethod2(t.Prop5, t.Prop6),
    //... many properties
    OutputProp20 = LocalMethod(t.Prop94, t.Prop95, t.Prop96, t.Prop97, t.Prop98, t.Prop99)
}).ToList();

Properties list need many lines.

Expected New API:

return dbContext.MyTable
.Select(t => new MyOutputClass
{
    OutputProp1 = t.Prop1,
    OutputProp2 = EF.OfflineExp(() => LocalMethod1(t.Prop2, t.Prop3, t.Prop4)),
    OutputProp3 = EF.OfflineExp(() => LocalMethod2(t.Prop5, t.Prop6)),
    //... many properties
    OutputProp20 = EF.OfflineExp(() => LocalMethod(t.Prop94, t.Prop95, t.Prop96, t.Prop97, t.Prop98, t.Prop99))
}).ToList();

The translator only extracts the member field attributes used in EF.OfflineExp and translates them into the fields for query, and uses the attribute values ​​to calculate the expression offline after the query returns.

[cincuranet]

This is not as straightforward as it looks like and might create a lot of hidden traps and challenges.

One "simple" example:

return dbContext.MyTable
.Select(t => new MyOutputClass
{
    OutputProp1 = t.Prop1,
    OutputProp2 = EF.OfflineExp(() => LocalMethod1(t.Prop2, t.Prop3, t.Prop4)),
    OutputProp3 = EF.OfflineExp(() => LocalMethod2(t.Prop5, t.Prop6)),
    //... many properties
    OutputProp20 = EF.OfflineExp(() => LocalMethod(t.Prop94, t.Prop95, t.Prop96, t.Prop97, t.Prop98, t.Prop99))
})
.GroupBy(x => x.OutputProp2)
.ToList();

I'm not sure this would lead into pit of success story. But we'll discuss it.

[Flithor]

@cincuranet :thinking: Translating the GroupBy statement actually converts this property as its assignment expression and analysis. The expression containing EF.OfflineExp is cannot be translated in GroupBy, and in fact, GroupBy has many limitations in LinqToSql. Many untranslatable expressions can be found in the visitor and throw exceptions to break off the translation. The design of EF.OfflineExp is to force the statement that the expression is calculated offline, and only the entity attributes need to be extracted into query without translation.

---

Extension idea: Maybe we can also provide "offline replacement", that is, if the expression can be translated, translate it, and if it cannot be translated, workaround it with offline mode.

//...
.Select(t => new MyOutputClass
{
    OutputProp1 = t.Prop1,
    OutputProp2 = EF.OfflineExp(() => Math.Abs(t.Prop2 + t.Prop3 / t.Prop4), offlineWorkaround: true),
    //...
})
//...

No no no, bad idea.

---

Or as an alternative, provide a hybrid offline Select. It supports the use of online Select to extract table fields into query, but the property assignment expression is always work in offline, and return IEnumerable<TOut>. This is equivalent to further simplifying the above expression, and there is no possible trouble caused by use online method.

return dbContext.MyTable
.SelectEnumerable(t => new MyOutputClass
{
    // extract columns required in expression, not all column for table:
    // SELECT Prop1,Prop2,Prop3,Prop4,...,Prop99 FROM MyTable

    OutputProp1 = t.Prop1, //offline assignment
    OutputProp2 = LocalMethod1(t.Prop2, t.Prop3, t.Prop4), //offline assignment
    OutputProp3 = LocalMethod2(t.Prop5, t.Prop6), //offline assignment
    //... many properties
    OutputProp20 = LocalMethod(t.Prop94, t.Prop95, t.Prop96, t.Prop97, t.Prop98, t.Prop99) //offline assignment
})
//IEnumerable<MyOutputClass>
.GroupBy(x => x.OutputProp2)
.ToList();

[cincuranet]

So far I'm not convinced that the SelectEnumerable adds significant value or simplifies greatly something over Selects and AsEnumerable.

[roji]

@Flithor I'm a bit confused... You can already mix server- and client-evaluation in the final Select of a LINQ query - witout specifying AsEnumerable():

_ = await context.Blogs
    .Select(b => new { Id = b.Id + 8, Name = Func(b.Name) })
    .ToListAsync();

In the above, b.Id + 8 gets translated to SQL (server evaluation), but Func(b.Name) gets evaluated client-side. So I'm not sure what you're looking for that EF doesn't already do, and why anything additional like EF.OfflineExp or SelectEnumerable is required.

Full code sample

```c# class Program { public static async Task Main() { await using var context = new BlogContext(); await context.Database.EnsureDeletedAsync(); await context.Database.EnsureCreatedAsync(); _ = await context.Blogs .Select(b => new { Id = b.Id + 8, Name = Func(b.Name) }) .ToListAsync(); } private static string Func(string s) => s + "foo"; } public class BlogContext : DbContext { public DbSet Blogs { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder .UseSqlServer("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false") // .UseNpgsql("Host=localhost;Username=test;Password=test") .LogTo(Console.WriteLine, LogLevel.Information) .EnableSensitiveDataLogging(); protected override void OnModelCreating(ModelBuilder modelBuilder) { } } public class Blog { public int Id { get; set; } public string Name { get; set; } public List Posts { get; set; } } public class Post { public int Id { get; set; } public string Title { get; set; } public Blog Blog { get; set; } } ```

[Flithor]

@roji The original intention was I find some databases in the development stage had translation issues with statements (not support translat some statements or translated statements cannot be executed in database). However, this is actually the responsibility of the developers of dialect library and database, not EF.