The same code as in my previous post (Data compare odditiy). If I add the part in bold (which filters further the query) I get an exception:

                    List<PostAndTagsModel> all;
                    var query = from p in ctx.Post
                                where !p.IsDeleted
                                select p;
                    [b]query = from p in query
                            where p.PostTags.Any(pt => tagsSet.Contains(pt.TagViewId))
                            select p;[/b]
                    query = from p in query
                            orderby p.Created descending
                            select p;
                    var pageQuery = query.Skip(page * pageSize).Take(pageSize);
                    var withTags = from p in pageQuery
                                   select new PostAndTagsModel
                                   {
                                       Post = p,
                                       Tags = (from pt in p.PostTags
                                               from tv in ctx.TagsView
                                               where tv.Id == pt.TagViewId && tv.IsActive && (tv.StartDate == null || tv.StartDate < p.Created) && (tv.EndDate == null || tv.EndDate > p.Created)
                                               select tv).ToList()
                                   };
                    all = withTags.ToList();

The top SQL produced (the one non-nested) seems wrong as it tries to wrongly get TagViewId (in bold - actually it is just bold markdown).

SELECT TOP(@p2) [LPLA_1].[Id],
                [LPLA_1].[AuthorId],
                [LPLA_1].[LastChangeUserId],
                [LPLA_1].[Title],
                [LPLA_1].[Body],
                [LPLA_1].[StartDate],
                [LPLA_1].[IsDeleted],
                [LPLA_1].[Created],
                [LPLA_1].[LastChange],
                [LPLA_1].[UrlTitle],
                1 AS [LPFA_5],
                [b][LPLA_2].[TagViewId][/b]
FROM   [Kdng].[dbo].[Posts] [LPLA_1]
WHERE  ((((((((NOT ([LPLA_1].[IsDeleted] = @p3))
          AND EXISTS
              (SELECT [LPLA_2].[TagViewId]
               FROM   [Kdng].[dbo].[PostTags] [LPLA_2]
               WHERE  ([LPLA_1].[Id] = [LPLA_2].[PostId]
                   AND ([LPLA_2].[TagViewId] IN (@p4)))))))))))
ORDER  BY [LPLA_1].[Created] DESC 

v3.5 Final - December 11th, 2012

Hi Miha,

• Could you please try with the lastest v3.5 version?
• What exactly is the exception message and stack trace?
• What is tagsSet? (I assume a List of int's/long's)

1. updated to the latest version (jan 17th)
2. T-SQL error: The multi-part identifier "LPLA_2.TagViewId" could not be bound (it is trying to retrieve TagViewId from EXISTS' SELECT)
3. TagsSet - list of strings actually (as is TagViewId)

• Try to simplify the query to the simplest form which reproduces the problem.
• post the full query
• get rid of the nested query and paging for reproducing the problem.
• It's a good thing to add paging directives AFTER the full query has been constructed anyway, otherwise you run the risk they're wrapped and ignored, as paging an inner query isn't possible: the outer query is always paged.

Will simplify, but quickly - it is the nested query that makes the inclusion of the problematic field. Without the nested query the field is not a part of the select and it works.

Frans, here is somehow simplified version

var t2 = from p in ctx.Post
                             where p.PostTags.Any(pt => new string[]{"one","two"}.Contains(pt.TagViewId))
                             select new {
                                Tags = (from pt in p.PostTags
                                        from tv in ctx.TagsView
                                        where tv.Id == pt.TagViewId
                                       select tv).ToList()
                             };

Could be problem because there is no relation known to LLBLGenPro between PostTags and TagsView? Post to PostTag is 1-many. PostTag - TagsView is what, many - many without intermediate table?

Pseudo tables

Post { Id }

PostTag { Post.Id, TagsView.Id }

TagsView { Id <- this is not a key nor it is unique, StartDate, EndDate }

Now, for this latest simplified query, what is the produced SQL?

Stripped all fields except for the problematic one

SELECT 1 AS [LPFA_4],
       [LPLA_2].[TagViewId]
FROM   [Kdng].[dbo].[Posts] [LPLA_1]
WHERE  ((((EXISTS
           (SELECT [LPLA_2].[TagViewId]
            FROM   [Kdng].[dbo].[PostTags] [LPLA_2]
            WHERE  ([LPLA_1].[Id] = [LPLA_2].[PostId]
                AND ([LPLA_2].[TagViewId] IN (@p1, @p2)))))))) 

I guess the problem has the same roots as described in my other thread -** Data compare oddity** and can't be solved this way.

In Linq, why don't you move the inner from and where to be joined with the outer one.

Something like:

var t2 = from p in ctx.Post
          from pt in p.PostTags
          from tv in ctx.TagsView
          where tv.Id == pt.TagViewId
          and pt.Any(pt => new string[]{"one","two"}.Contains(pt.TagViewId))
...

Hi Walaa,

Because it is a nested query - I was selecting posts and their tagviews. The latest one is a stripped down version for problem solving purposes.

I think I can reproduce it using my example in your other thread:

RTL ORMSupportClasses: 3.5.13.108 LinqSupportClasses: 3.5.12.1211

Reproduce code

[TestMethod]
public void WeirdCorrelationInnerQuery2()
{
    var adapter = new DataAccessAdapter();
    var ctx = new LinqMetaData(adapter);
            
    var x = from sm in ctx.ShipMethod
            where sm.PurchaseOrdes.Any(po => new int[] { 2, 5 }.Contains(po.VendorId))
            select new
            {
                Vendors = (from po in sm.PurchaseOrdes
                            from v in ctx.Vendor 
                            where v.VendorId == po.VendorId 
                            select v).ToList()
            };

    var results = x.ToList();
}

Generated SQL

SELECT 1                   AS [LPFA_4],
       [LPLA_2].[VendorID] AS [VendorId]
FROM   [AdventureWorks].[Purchasing].[ShipMethod] [LPLA_1]
WHERE  ((((EXISTS
           (SELECT [LPLA_2].[VendorID] AS [VendorId]
            FROM   [AdventureWorks].[Purchasing].[PurchaseOrderHeader] [LPLA_2]
            WHERE  ([LPLA_1].[ShipMethodID] = [LPLA_2].[ShipMethodID]
                AND ([LPLA_2].[VendorID] IN (@p1, @p2)))))))) 

Exception (stack trace attached)

Exception type: System.Data.SqlClient.SqlException Message: The multi-part identifier "LPLA_2.VendorID" could not be bound.

The weird thing is that apparently the code shouldn't work at all because the inner query projection ('v' as Vendors) doesn't have any correlated field that links to the parent query. If you remove the filter on the parent query...

 var x = from sm in ctx.ShipMethod              
        select new
        {
            Vendors = (from po in sm.PurchaseOrdes
                        from v in ctx.Vendor 
                        where v.VendorId == po.VendorId 
                        select v).ToList()
        };

... the exception is different:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException: A nested query in the projection has no correlation filter to tie its set to the containing parent row. Please add a correlation filter to the where clause of this query to tie the nested query to the parent.

Maybe you should use a PrefetchPath here...

Yep, Frans explained the issue in Data compare oddity thread. It was another problem but the cause is the same I think. Prefetching works.

Yep, same cause. Nested queries can't be tied together properly.