I am experiencing what seems to be an edge case bug, when doing a Linq query, combining joins and an EXISTS clause on a view.

I'll try to explain with examples.

3 entities and one view is involved

• MasterEntity (id, text)
• Detail1Entity (id)
• Detail2Entity (id)
• AccessView (id) -TypedView

I want to perform a query as such:

var q = from m in linqMetaData.Master
        where linqMetaData.Accessview.Any(x => x.Id == m.Id)
        join d1 in linqMetaData.Detail1 on m.Id equals d1.Id into join1
        join d2 in linqMetaData.Detail2 on m.Id equals d2.Id into join2
        from j1 in join1.DefaultIfEmpty()
        from j2 in join2.DefaultIfEmpty()
        select m.Text;
var rows = await q.ToListAsync();

However, I get exception which state it is referencing non existing column: column LPA_L1.text does not exist. The query is:

SELECT "LPA_L1"."text" as "Text"
from
((
 select
    "LPA_L1"."text" as "Text"
from
    ((
    select
        "LPA_L5"."id" as "Id",
        :p2 as "LPFA_6"
    from
        ("public"."master" "LPA_L4"
    left join "public"."detail1" "LPA_L5" on
        "LPA_L4"."id" = "LPA_L5"."id")
    where
        ( exists (
        select
            "LPA_L6"."Id"
        from
            (
            select
                "LPLA_2"."id" as "Id"
            from
                "public"."accessview" "LPLA_2") "LPA_L6"
        where
            ( ( "LPA_L6"."Id" = "LPA_L4"."id"))))) "LPA_L1"
left join "public"."detail2" "LPA_L2" on
    "LPA_L1"."Id" = "LPA_L2"."id")

However, if I change code generation so the access view is an entity instead of a typed view, the same query works, with the expected query generated as:

select
    "LPA_L1"."text" as "Text"
from
    (("public"."master" "LPA_L1"
left join "public"."detail1" "LPA_L2" on
    "LPA_L1"."id" = "LPA_L2"."id")
left join "public"."detail2" "LPA_L3" on
    "LPA_L1"."id" = "LPA_L3"."id")
where
    ( ( ( exists (
    select
        "LPLA_2"."id" as "Id"
    from
        "public"."accessview2" "LPLA_2"
    where
        ( ( "LPLA_2"."id" = "LPA_L1"."id"))))))

Also note, that the problem only exists when I am having 2 joins. I can also solve the issue by removing one of the joins (but I need both).

I am using LLBLGen 5.8.1.

Would it work if you move the Exists clause (where any) after all joins?

Hi mbp,

Please explain a little more Why can't you put the where clause after the joins. Give us more real code if necessary.
You also could use the Fluent variation which allows to to:

var q =  (from m in linqMetaData.Master
        join d1 in linqMetaData.Detail1 on m.Id equals d1.Id into join1
        join d2 in linqMetaData.Detail2 on m.Id equals d2.Id into join2
        from j1 in join1.DefaultIfEmpty()
        from j2 in join2.DefaultIfEmpty());

q = q.Where(....);

If you need to standarize the predicate you could use something like PredicateBuilder if you want to stick with Linq.

It's also a good choice to use QuerySpec which gives you more accurate results based on how you build the query.

Hmm, where did my comment go?! I see you replied to it, but comment disappeared.

daelmo wrote:

Hi mbp,

Please explain a little more Why can't you put the where clause after the joins. Give us more real code if necessary.

The reason is I also have a custom "Select" in the end, which is referring to the joins, e.g.:

var q = from m in linqMetaData.Master
    join d1 in linqMetaData.Detail1 on m.Id equals d1.Id into join1
    join d2 in linqMetaData.Detail2 on m.Id equals d2.Id into join2
    from j1 in join1.DefaultIfEmpty()
    from j2 in join2.DefaultIfEmpty()
    select new CustomDto { Text = m.Text, Id = j1.Id };

// This does not work, since I want to Where on some column that is not part of the Select.
q = q.Where(x => linqMetaData.Accessview.Any(y => x.Id == y.Id && x.ColumnNotPartOfSelect == "something"));

daelmo wrote:

If you need to standarize the predicate you could use something like PredicateBuilder if you want to stick with Linq.

Yes, I have something similar to generate a Where clause. It worked wonders, until I came across this edge case.

Below you can see some more of my code, although it is still simplified:

{
    var linqMetaData = new LinqMetaData(adapter);

    var rows = await ExecuteQuery(linqMetaData.Master, q => from m in q
                                                    join d1 in linqMetaData.Detail1 on m.Id equals d1.Id into join1
                                                    join d2 in linqMetaData.Detail2 on m.Id equals d2.Id into join2
                                                    from j1 in join1.DefaultIfEmpty()
                                                    from j2 in join2.DefaultIfEmpty()
                                                    select new CustomDto { Text = m.Text, Id = j1.Id });

    Console.WriteLine(rows.Count);
}


private static async Task<List<CustomDto>> ExecuteQuery(IQueryable<MasterEntity> queryable, Func<IQueryable<MasterEntity>, IQueryable<CustomDto>> targetQueryBuilder)
{
    var linqMetaData = new LinqMetaData();
    var filterClause = GenerateFilterClause(linqMetaData);
    var q = queryable.Where(filterClause);
    var q2 = targetQueryBuilder(q);
    return await q2.ToListAsync();
}


// This method is really implemented in a generic way, building up expression depending on entity type.
private static Expression<Func<MasterEntity, bool>> GenerateFilterClause(LinqMetaData linqMetaData)
{
    return (MasterEntity x) => linqMetaData.Accessview.Any(y => x.Id == y.Id);
}

Is it not possible to solve the bug with the typed view vs entity?

The typed view doesn't have relationship information and therefore the correlation relationship that's used in the entity situation isn't used, instead the one you provided is used. The Any() isn't needed I think, as you really just want to have all masters where there's an accessview. You can do that with an inner join, or left join with a null check predicate in a where?

Otherwise I'm not sure why the Any()'s there and not a normal predicate. The main issue with any/where before a join is that they're lifted out of the join side (here the from m in masters) and put at the end. Any() is also a nasty method to convert as it's not 1:1 convertible to SQL so it has to be interpreted and as it references elements that are in another scope, it sometimes goes wrong.

Otis wrote:

The typed view doesn't have relationship information and therefore the correlation relationship that's used in the entity situation isn't used, instead the one you provided is used.

In my example here - the is also no relationship between the various entities. I didn't create any foreign keys between them. That has nothing to do with it being a view.

Otis wrote:

The Any() isn't needed I think, as you really just want to have all masters where there's an accessview. You can do that with an inner join, or left join with a null check predicate in a where?

Otherwise I'm not sure why the Any()'s there and not a normal predicate. The main issue with any/where before a join is that they're lifted out of the join side (here the from m in masters) and put at the end. Any() is also a nasty method to convert as it's not 1:1 convertible to SQL so it has to be interpreted and as it references elements that are in another scope, it sometimes goes wrong.

Doing an Any() for doing an EXISTS query is what I find is recommended. Also, it fits very nice with my problem of having to attach it to an existing query.

I appreciate any advice about refactoring my code, so I can continue. But I did find a bug in LLBLGen, which I will fear that any other developers might come across.

mbp wrote:

Otis wrote:

The typed view doesn't have relationship information and therefore the correlation relationship that's used in the entity situation isn't used, instead the one you provided is used.

In my example here - the is also no relationship between the various entities. I didn't create any foreign keys between them. That has nothing to do with it being a view.

It was what I could think of why things are different in this case. DefaultIfEmpty + any + joins in a query is a complex affair so it can go wrong in many places. We have to look at a minimal repro case to see where it really goes wrong. But as Any() is involved it's likely caused by the handler of that method.

Otis wrote:

The Any() isn't needed I think, as you really just want to have all masters where there's an accessview. You can do that with an inner join, or left join with a null check predicate in a where?

Otherwise I'm not sure why the Any()'s there and not a normal predicate. The main issue with any/where before a join is that they're lifted out of the join side (here the from m in masters) and put at the end. Any() is also a nasty method to convert as it's not 1:1 convertible to SQL so it has to be interpreted and as it references elements that are in another scope, it sometimes goes wrong.

Doing an Any() for doing an EXISTS query is what I find is recommended. Also, it fits very nice with my problem of having to attach it to an existing query.

That's true, however you have to realize that Linq isn't the best way to formulate SQL if you start with SQL, as in: 'I want to utilize an EXISTS predicate', isn't the way you should approach Linq, as a Linq query needs interpretation and isn't 1:1 convertible to SQL. In your query above, I don't see why Any() is used (or an EXISTS query), as you can use a normal join/predicate as well which results in a much simpler tree to convert.

I appreciate any advice about refactoring my code, so I can continue. But I did find a bug in LLBLGen, which I will fear that any other developers might come across.

Noted, though the edge case problems with Any() are known, or at least, we have logged some in our tracker though it's really hard to make it work 100% of the time. The simple approach of Any() is working OK, it's in combination of other elements, nested deep in the tree like you have now, where things go wrong. In general nesting predicates between Joins in a Linq query is always leading to suboptimal trees as these have to be lifted. So it comes down to this:

var q = from m in linqMetaData.Master
    join d1 in linqMetaData.Detail1 on m.Id equals d1.Id into join1
    join d2 in linqMetaData.Detail2 on m.Id equals d2.Id into join2
    from j1 in join1.DefaultIfEmpty()
    from j2 in join2.DefaultIfEmpty()
    where linqMetaData.Accessview.Any(x => x.Id == m.Id)
    select new CustomDto { Text = m.Text, Id = j1.Id };

This is the minimal form of which fails btw? Removing anything from the query makes it work?

Otis wrote:

var q = from m in linqMetaData.Master
    join d1 in linqMetaData.Detail1 on m.Id equals d1.Id into join1
    join d2 in linqMetaData.Detail2 on m.Id equals d2.Id into join2
    from j1 in join1.DefaultIfEmpty()
    from j2 in join2.DefaultIfEmpty()
    where linqMetaData.Accessview.Any(x => x.Id == m.Id)
    select new CustomDto { Text = m.Text, Id = j1.Id };

This is the minimal form of which fails btw? Removing anything from the query makes it work?

Almost. The "where" must come before the joins in order for it to fail.

I can send you a complete minimal solution if you want it. Just need to know where to send it.

Please zip a repro without the binaries, and attach it to a reply message in this thread.

Attachment

OK, we'll have a look. For your query, I'd move the where to the outside of the query, as that's where it ends up through the provider as well. It might very well be this bug isn't fixable at this point but we'll see what we can do.

The difference in handling of the elements is caused by the fact that the extractor visitor sees a 'Select' in the query with a typed view (as it's converted to a select on a typed view) and therefore decides it can't extract the where clause from the joins. The main reason is that inside the select (which is another scope) it can reference outside elements in the query but extracting the whole where tree at that point could lead to references to elements that will be folded later on into subqueries (and the resulting query crashes) so we leave them as-is. This is usually ok, but the problem with your query is that the where expression has as source another join, so not extracting the where leaves that join as the source of the where, which is then becoming part of that, resulting in the mess. There's no way to fix that, if the where is kept as-is, it has to be a contained query, but here it's not and therefore should be moved.

We're looking into whether we can detect if the select we're seeing is a typedview originating select, as we then know there's no correlation predicate in the query (as the provider itself generated it) so we can safely move it.

Fixed. See 5.8.3 hotfix. It's still recommended to move the where outside the joins, as it helps the provider to generate the proper query, as sometimes a where can't be lifted from the join due to it having a projection. This cause it to be combined with the joins which fails as we've seen here.

Otis wrote:

Fixed. See 5.8.3 hotfix. It's still recommended to move the where outside the joins, as it helps the provider to generate the proper query, as sometimes a where can't be lifted from the join due to it having a projection. This cause it to be combined with the joins which fails as we've seen here.

Amazing. I can confirm it works!

Great!