NotSupportedException when using DbInjectable within a sub query about nein-linq HOT 13 CLOSED

For now I've ended up with an extension method that directly rewrites the query, skipping the RewriteQueryable. It pretty much does what I want and the includes work that way:

public static IQueryable<T> Rewrite<T>(this IQueryable<T> query)
{
    var expression = new InjectableQueryRewriter().Visit(query.Expression);
    query = query.Provider.CreateQuery<T>(expression);
    return query;
}

from nein-linq.

Hi,

Is it intended, that you're mixing up two queries? In the example as well as the test you provided you do so:

    //First query
    var foo = dbContext.Set<Foo>().ToDbInjectable();

    //Second query
    //First query also gets referenced in second line ('foo')
    var query = from bar in dbContext.Set<Bar>()
            where foo.Any(f => f.SomeInjectLambdaMethod())
            select bar;

    //First query
    var dummies = db.Dummies.ToDbInjectable();

    //Second query
    //The first query also gets referenced in the second line ('dummies')
    var query = from dummy in db.Dummies
                where dummies.Any(d => d.Id < dummy.Id)
                select dummy;

The exception thrown by EF also indicates, that there are 2 separate queries involved.

from nein-linq.

Yes, this is intended. It basically translates to a SELECT * FROM Bar WHERE EXISTS (SELECT * FROM Foo WHERE ...)-Sql statement.

For the test I used the same entity for simplification - in the real world scenario a different table gets queried.

[Edit] If you mean the seperate variables - that doesn't affect the result. If you replace the call to foo.Any() with dbContext.Set<Foo>().ToDbInjectable().Any(...) you get the same issue.

from nein-linq.

Thanks for providing a failing test! ❤️

Using .ToDbInjectable() within a sub query seems to be a problem (async or no async), although I think I'm using this somewhere already...

Using it on the outer query only works:

[Fact]
public async Task AnyShouldSucceed()
{
    var dummies = db.Dummies;
    var query = from dummy in db.Dummies.ToDbInjectable()
                where dummies.Any(d => d.Id < dummy.Id)
                select dummy;

    var result = await query.ToListAsync();

    Assert.Equal(2, result.Count);
}

I'll look into it later.

from nein-linq.

Okay, Entity Framework seems to require a "real" DbSet object for sub queries, while Entity Framework Core can handle any query-able object (however).

Exposing the actual query-able for entity framework does help (see ecb845f), but isn't nice. But I'm not giving up yet.

Note: in order to use lambda injection the call to .ToDbInjectable() is only necessary on the outer query, since only the outer query parses the actual expression tree.

A fix would only make sense for scenarios, where the inner query

1. should be able to use lambda injection "as it is"
2. should be use-able as inner query as well

// should work
var query1 = db.Dummy.Where(...)
var query2 = from d in db.Dummy.ToDbInjectable()
             where query1.Any(... query1 ...)
             select d;

var result = query2.ToList();

// should work too
var query1 = db.Dummy.Where(...)

var result = query1.ToDbInjectable().ToList();

// doesn't work (yet)
var query1 = db.Dummy.ToDbInjectable().Where(...)
var query2 = from d in db.Dummy.ToDbInjectable()
             where query1.Any(... query1 ...)
             select d;

var result = query2.ToList();

from nein-linq.

I also came around that solution when taking a deep dive - students and their spare time, you know... But while the fix resolves issues with sub queries, that are referenced as variables, it will still encounter an error when directly nested:

        [Fact]
        public void DirectlyNestedSubQueryShouldSucceed()
        {
            // TODO: make it nice
            
            var query = from dummy in db.Dummies
                where db.Dummies.ToDbInjectable().Any(d => d.Id < dummy.Id)
                //also doesn't work as '((RewriteQueryable<Dummy>)db.Dummies.ToDbInjectable()).Queryable.Any(d => d.Id < dummy.Id)'
                select dummy;

            //Produces a 'cannot translate ToDbInjectable() to ...'
            var result = query.ToList();

            Assert.Equal(2, result.Count);
        }

This is due to the fact, that the expression tree will contain a method cal expression to DbRewrite/ToDbInjectable/ToInjectable:

.Call Where(
    .Call .Constant<ObjectQuery<Dummy>>(
        ObjectQuery<Dummy>
    ).MergeAs(
        .Constant<MergeOption>(
            AppendOnly
        )
    ),
    .Call Any(
        .Call RewriteDbQueryBuilder.DbRewrite(
            .Constant<ReplacementDbQueryWrapper<Dummy>>(
                ReplacementDbQueryWrapper<Dummy>
            ).Query,
            .Constant<<RealTest+<>c__DisplayClass8_0>(
                <RealTest+<>c__DisplayClass8_0>
            ).rewriter
        ),
        $d.Id < $dummy.Id
    )
)

I haven't yet found an approach, which isn't exremely ugly and/or extremely error-prone. But also I don't know if that's even a valid use case.

from nein-linq.

So, I did a thing.

If the outer query is an injectable (or any other NeinLinq) query too, it is now able to detect other NeinLinq queries and resolve them before passing the expression tree to the underlying query provider. Thus, the outer query must be .ToWhatever() too in order to make that work.

I'm currently not on windows, but this test succeeds on AppVeyor:

[Fact]
public async Task SubQueryShouldSucceed()
{
    var innerRewriter = new Rewriter();
    var outerRewriter = new Rewriter();

    var dummies = db.Dummies.DbRewrite(innerRewriter);

    var query = from dummy in db.Dummies.DbRewrite(outerRewriter)
                where dummies.Any(d => d.Id < dummy.Id)
                select dummy;

    var result = await query.ToListAsync();

    Assert.True(outerRewriter.VisitCalled);
    Assert.True(innerRewriter.VisitCalled);
    Assert.Equal(2, result.Count);
}

Have a look, please (branch issue-11).

I'll open an PR for review, when I'm sure what I'm doing here...

from nein-linq.

@jhartmann123 does that help?

from nein-linq.

@axelheer Thanks for your quick response and sorry for my late response, didn't have the time to look into it last week.

It fixes the NotSupportedException, but somehow messes with my .Includes. Some navigational properties don't seem to be loaded - I get null references on properties that get properly loaded when not using NeinLinq on that query.

Unfortunatly when trying to write a repro/unit test for that case, the test succeeded. So maybe it's sth. on our side... I'll let you know as soon as I know more.

from nein-linq.

Just let me know, when you have some details about your .Includes.

from nein-linq.

Ah, the tests use the same context object for initialization and testing. Thus, all the entity objects are already there. I changed that and added two basic .Include tests.

Can you try to reproduce it, again?

from nein-linq.

It fails if you do the .Include() after the rewrite:

[WindowsFact]
public async Task IncludeShouldSucceed()
{
    var query = from dummy in db.Dummies.DbRewrite(new Rewriter())
                select dummy;

    var result = await query.Include(q => q.Other).ToListAsync();

    Assert.All(result, r => Assert.Equal(r.Name, r.Other.Name));
}

from nein-linq.

@jhartmann123 any further comments? Do my explanations regarding your tests make sense?

I think we've done, what can be done.

from nein-linq.