Trying to add Support For Index Usage on EFCore Spanner Provider

Question

I'm writing a driver for EF Core for Spanner - In basic level it works and I can write Read and Write Queries that get's translated to Spanner SQL , executed and return results etc..

Now I'm trying to add Support For Read Query with Secondary Index.

Ultimately I'm trying to generate this SQL Query: 
SELECT * FROM PostTags@{ FORCE_INDEX = PostTagsByTagId } WHERE TagId = 1

From This Linq:
var postTag = ctx.PostTags.WithIndex("PostTagsByTagId").Where(x => x.TagId == 1).FirstOrDefault();

I've added extension method as follow:

public static class SpannerIndexSupport
{
    public static IQueryable<TSource> WithIndex<TSource>(this IQueryable<TSource> query, string indexName)
    {
        var methodDefinition = typeof(SpannerIndexSupport).GetTypeInfo().GetMethods().Single(m => m.Name == "WithIndex");
        var method = methodDefinition.MakeGenericMethod(typeof(TSource));
        var args = new[] { query.Expression, Expression.Constant(indexName) };
        var expression = Expression.Call(null, method, args);
        return query.Provider.CreateQuery<TSource>(expression);
    }
}

And tried to write IAsyncQueryProvider to support it but couldn't find a way to make it work. Any ideas Anyone?

score 0 · Answer 1 · answered Jan 25 '18 at 20:08

0

In the official Spanner EFCore library (https://github.com/GoogleCloudPlatform/google-cloud-dotnet/tree/master/apis/Google.Cloud.EntityFrameworkCore.Spanner/Google.Cloud.EntityFrameworkCore.Spanner), I would start by overriding VisitTable(TableExpression tableExpression) in SpannerQuerySqlGenerator:

https://github.com/GoogleCloudPlatform/google-cloud-dotnet/tree/master/apis/Google.Cloud.EntityFrameworkCore.Spanner/Google.Cloud.EntityFrameworkCore.Spanner/Query/Sql/Internal/SpannerQuerySqlGenerator.cs

This will allow you to get a proof of concept going because you can directly influence the generated SQL text there. Once that works, then you will want to make it proper.

I suppose there might be a few ways to make this work. The simplest might be to have some custom no-op method marker in the Linq expression tree and then register an IMethodCallTranslator to convert it either to a custom spanner specific Expression (whose Accept calls into SqlGenerator to generate the proper Sql) or possibly creating a SqlTranslatingExpressionVisitor to switch out the table expression to a custom one that allows the FORCE_INDEX.

Sorry I couldn't help more.

answered Jan 25 '18 at 20:08

Benjamin Wulfe

1,705
13
9

Hey, Thanks a lot for your feedback, Indeed my plan was to modify the visitTable method. The problem I've encountered was how do I signal the visitTable method which and when to perform the modification. This is actually the second part of your answer which I'm not sure how to overcome it yet. I have tried some of your suggested solutions and some others but couldn't make it work at the moment.. I'm currently researching it and digging into EF Code. I will Update once I'll have something working :) – Al Yaros Jan 26 '18 at 21:44
Quick Update - I've made it work with a Dirty Hack - But at least it works for now :) - I used the "SpannerUseIndexTranslator : IMethodCallTranslator" with an argument I.e. ' var results = (from p in ctx.PostTags where p.TagId == 1 && p.TagId.UseIndex("PostTags", "AAA") select p).FirstOrDefault();' Which will soon change to x=>x.SomeThing With Attribute Instead of providing strings to UseIndex. Very Limited & Hacked Just for now :) – Al Yaros Jan 26 '18 at 22:55
Sounds like you are making great progress! I like your first form better, but I guess you cannot capture a WithIndex() in a methodcalltranslator or membertranslator? There is also a visitor pattern if I remember that might be able to visit the entire expression tree and transform it. I don't believe the spanner efcore provider registers one yet. By the way, we appreciate any PRs if you like once you are done. – Benjamin Wulfe Jan 27 '18 at 01:32
I see. This is definitely not easy. returning the type as a bool isn't quite ideal. How about the possibility of defining your own expression similar to how ILike is done in postgres (see https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/blob/master/src/EFCore.PG/Query/Expressions/Internal/ILikeExpression.cs). When you return your own custom expression, you can tightly control the generated SQL and simply emit no extra calls at the column. Maybe the sqlgenerator for that expression can store some state to spit out index usage at the right place? – Benjamin Wulfe Jan 29 '18 at 17:50
Thanks! I will analyze your suggestion and update with the findings. – Al Yaros Jan 30 '18 at 19:05

score 0 · Answer 2 · answered Feb 14 '23 at 14:08

This is now supported in the official Entity Framework provider for Google Cloud Spanner. You can add this by adding a tag to the query like this:

var singersOrderedByFullName = context.Singers
    // This will add the following comment to the generated query:
    // `-- Use hint: force_index FullName`
    // This comment will be picked up by the interceptor and an index
    // hint will be added to the query that is executed.
    .TagWith("Use hint: force_index FullName")
    .OrderBy(s => s.FullName)
    .AsAsyncEnumerable();

A full example can be found here: https://github.com/googleapis/dotnet-spanner-entity-framework/blob/main/Google.Cloud.EntityFrameworkCore.Spanner.Samples/Snippets/QueryHintSample.cs

Trying to add Support For Index Usage on EFCore Spanner Provider

2 Answers2