I'm using version 3.1, adapter

So I have a parent/child relationship within the same table & I'm trying to select the child information - for some reason the below is retrieving the c1 (parent) entity values instead of the c2(child) entity values. Am I writing the linq query wrong ?

Dim recs = (From c1 In _linqAdapterData001.UOffCategory _ Join c2 In _linqAdapterData001.UOffCategory _ On c2.ParentCategoryId Equals c1.CategoryId _ Join ic In _linqAdapterData001.UOffItemCategory _ On c2.CategoryId Equals ic.CategoryId _ Where ic.ItemNo = ItemNo _ AndAlso c1.Description = "Accessory Category" _ AndAlso c1.ParentCategoryId Is Nothing _ Select c2)

So this is how I would write the query directly:

SELECT c1.* FROM u_off_categories c1 JOIN u_off_categories c2 ON c1.category_id = c2.parent_category_id JOIN u_off_item_categories ic ON c2.category_id = ic.category_id WHERE c1.description = 'Accessory Category' AND c1.parent_category_id IS NULL AND ic.item_no = '501449'

Any help would be greatly appreciated.
Thanks !

Actually I think maybe this thread is the cause of my problem.

http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=20417

diamondracer wrote:

Actually I think maybe this thread is the cause of my problem.

http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=20417

That doesn't seem to be the case. You are doing something perfectly normal in my opinion. Please post your LLBLGen runtime library version and the generated SQL for that code (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=12769).

Also, I'm a little confused. For me, your linq query and your posted SQL are opposite. Your linq query are fetching the child. Your wanted SQL is fetching the parents. Am I right?

Ok thanks it looks like my runtime version is v2.0.50727

Here is the generated sql, which is definately not what I want:

SELECT DISTINCT [lpa_l1].[BsiCatalogNo], [lpa_l1].[BsiClassId], [lpa_l1].[BsiClassIdChar], [lpa_l1].[BsiFlag], [lpa_l1].[CatalogName], [lpa_l1].[CategoryId], [lpa_l1].[Context], [lpa_l1].[Description], [lpa_l1].[DisplayWeb], [lpa_l1].[ImageUrl], [lpa_l1].[ParentCategoryId] FROM (SELECT [lpa_l2].[bsi_catalog_no] AS [BsiCatalogNo], [lpa_l2].[bsi_class_id] AS [BsiClassId], [lpa_l2].[bsi_class_id_char] AS [BsiClassIdChar], [lpa_l2].[bsi_flag] AS [BsiFlag], [lpa_l2].[catalog_name] AS [CatalogName], [lpa_l2].[category_id] AS [CategoryId], [lpa_l2].[context] AS [Context], [lpa_l2].[description] AS [Description], [lpa_l2].[display_web] AS [DisplayWeb], [lpa_l2].[image_url] AS [ImageUrl], [lpa_l2].[parent_category_id] AS [ParentCategoryId], [lpa_l3].[bsi_catalog_no] AS [BsiCatalogNo0], [lpa_l3].[bsi_class_id] AS [BsiClassId1], [lpa_l3].[bsi_class_id_char] AS [BsiClassIdChar2], [lpa_l3].[bsi_flag] AS [BsiFlag3], [lpa_l3].[catalog_name] AS [CatalogName4], [lpa_l3].[category_id] AS [CategoryId5], [lpa_l3].[context] AS [Context6], [lpa_l3].[description] AS [Description7], [lpa_l3].[display_web] AS [DisplayWeb8], [lpa_l3].[image_url] AS [ImageUrl9], [lpa_l3].[parent_category_id] AS [ParentCategoryId10], [lpa_l4].[attribute] AS [Attribute], [lpa_l4].[bsi_flag] AS [BsiFlag11], [lpa_l4].[catalog_name] AS [CatalogName12], [lpa_l4].[category] AS [Category], [lpa_l4].[category_id] AS [CategoryId13], [lpa_l4].[created_by] AS [CreatedBy], [lpa_l4].[creation_date] AS [CreationDate], [lpa_l4].[item_category_id] AS [ItemCategoryId], [lpa_l4].[item_no] AS [ItemNo], [lpa_l4].[subcategory] AS [Subcategory] FROM (([DATA_001].[dbo].[u_off_categories] [lpa_l2] INNER JOIN [DATA_001].[dbo].[u_off_categories] [lpa_l3] ON [lpa_l2].[category_id] = [lpa_l3].[parent_category_id]) INNER JOIN [DATA_001].[dbo].[u_off_item_categories] [lpa_l4] ON [lpa_l3].[category_id] = [lpa_l4].[category_id])) [lpa_l1] WHERE ((((([lpa_l1].[Description] = @p1)))))

I mistyped the wanted sql above.. it should have been the children:

SELECT c2.* FROM u_off_categories c1 JOIN u_off_categories c2 ON c1.category_id = c2.parent_category_id JOIN u_off_item_categories ic ON c2.category_id = ic.category_id WHERE c1.description = 'Accessory Category' AND c1.parent_category_id IS NULL AND ic.item_no = '501449'

The generated SQL is doing what the linq query is asking:

From c1 In _linqAdapterData001.UOffCategory _
                     Join c2 In _linqAdapterData001.UOffCategory _
                     On c2.ParentCategoryId Equals c1.CategoryId _

SELECT [lpa_l3].*...
          FROM (([DATA_001].[dbo].[u_off_categories] [lpa_l2]
         INNER JOIN [DATA_001].[dbo].[u_off_categories] [lpa_l3]
                 ON [lpa_l2].[category_id] = [lpa_l3].[parent_category_id])

The generated SQL you posted doesn't match completely with your linq query, some filters are missing:

...
Where ic.ItemNo = ItemNo _
                     AndAlso c1.Description = "Accessory Category" _
                     AndAlso c1.ParentCategoryId Is Nothing _
                     Select c2)

Are you actually running the exact linq query you posted in the first post?

sorry I was messing with it while debugging. Here is the exact sql generated from my first post:

SELECT DISTINCT [lpa_l1].[BsiCatalogNo], [lpa_l1].[BsiClassId], [lpa_l1].[BsiClassIdChar], [lpa_l1].[BsiFlag], [lpa_l1].[CatalogName], [lpa_l1].[CategoryId], [lpa_l1].[Context], [lpa_l1].[Description], [lpa_l1].[DisplayWeb], [lpa_l1].[ImageUrl], [lpa_l1].[ParentCategoryId] FROM (SELECT [lpa_l2].[bsi_catalog_no] AS [BsiCatalogNo], [lpa_l2].[bsi_class_id] AS [BsiClassId], [lpa_l2].[bsi_class_id_char] AS [BsiClassIdChar], [lpa_l2].[bsi_flag] AS [BsiFlag], [lpa_l2].[catalog_name] AS [CatalogName], [lpa_l2].[category_id] AS [CategoryId], [lpa_l2].[context] AS [Context], [lpa_l2].[description] AS [Description], [lpa_l2].[display_web] AS [DisplayWeb], [lpa_l2].[image_url] AS [ImageUrl], [lpa_l2].[parent_category_id] AS [ParentCategoryId], [lpa_l3].[bsi_catalog_no] AS [BsiCatalogNo0], [lpa_l3].[bsi_class_id] AS [BsiClassId1], [lpa_l3].[bsi_class_id_char] AS [BsiClassIdChar2], [lpa_l3].[bsi_flag] AS [BsiFlag3], [lpa_l3].[catalog_name] AS [CatalogName4], [lpa_l3].[category_id] AS [CategoryId5], [lpa_l3].[context] AS [Context6], [lpa_l3].[description] AS [Description7], [lpa_l3].[display_web] AS [DisplayWeb8], [lpa_l3].[image_url] AS [ImageUrl9], [lpa_l3].[parent_category_id] AS [ParentCategoryId10], [lpa_l4].[attribute] AS [Attribute], [lpa_l4].[bsi_flag] AS [BsiFlag11], [lpa_l4].[catalog_name] AS [CatalogName12], [lpa_l4].[category] AS [Category], [lpa_l4].[category_id] AS [CategoryId13], [lpa_l4].[created_by] AS [CreatedBy], [lpa_l4].[creation_date] AS [CreationDate], [lpa_l4].[item_category_id] AS [ItemCategoryId], [lpa_l4].[item_no] AS [ItemNo], [lpa_l4].[subcategory] AS [Subcategory] FROM (([DATA_001].[dbo].[u_off_categories] [lpa_l2] INNER JOIN [DATA_001].[dbo].[u_off_categories] [lpa_l3] ON [lpa_l2].[category_id] = [lpa_l3].[parent_category_id]) INNER JOIN [DATA_001].[dbo].[u_off_item_categories] [lpa_l4] ON [lpa_l3].[category_id] = [lpa_l4].[category_id])) [lpa_l1] WHERE ((((((([lpa_l1].[ItemNo] = @p1) AND ([lpa_l1].[Description] = @p2)) AND ([lpa_l1].[ParentCategoryId] IS NULL))))))

diamondrace wrote:

Ok thanks it looks like my runtime version is v2.0.50727

Mmm, that is not the RTL, see this. I just want you to use the latest version.

Ok, back to your odd query in your last post, I can't reproduce it:

var q = from parent in metaData.Employee
        join child in metaData.Employee on parent.EmployeeId equals child.ReportsTo
        where parent.ReportsTo == null
        select child;

SELECT 
    [LPA_L2].[Address], 
    [LPA_L2].[BirthDate], 
    [LPA_L2].[City], 
    [LPA_L2].[Country], 
    [LPA_L2].[EmployeeID] AS [EmployeeId], 
    [LPA_L2].[Extension], 
    [LPA_L2].[FirstName], 
    [LPA_L2].[HireDate], 
    [LPA_L2].[HomePhone], 
    [LPA_L2].[LastName], 
    [LPA_L2].[Notes], 
    [LPA_L2].[Photo], 
    [LPA_L2].[PhotoPath], 
    [LPA_L2].[PostalCode], 
    [LPA_L2].[Region], 
    [LPA_L2].[ReportsTo], 
    [LPA_L2].[Title], 
    [LPA_L2].[TitleOfCourtesy] 

FROM ( 
    [Northwind].[dbo].[Employees] [LPA_L1]  
    INNER JOIN [Northwind].[dbo].[Employees] [LPA_L2]  
        ON  [LPA_L1].[EmployeeID] = [LPA_L2].[ReportsTo]) 

WHERE ( ( ( ( ( [LPA_L1].[ReportsTo] IS NULL)))))

I see one odd thing in the linq query in your first post:

Dim recs = (From c1 In _linqAdapterData001.UOffCategory _
                     Join c2 In _linqAdapterData001.UOffCategory _
                     On c2.ParentCategoryId Equals c1.CategoryId _
                     ...)

I think it should be:

Dim recs = (From c1 In _linqAdapterData001.UOffCategory _
                     Join c2 In _linqAdapterData001.UOffCategory _
                     On c1.CategoryId Equals c2.ParentCategoryId  _
                     ...)

Please give it a try.