none
Left Join With Filtering Criteria... RRS feed

  • Question

  • I'm trying to write a linq query to select the records from a left table that doesn't have a related record in a right table (left outer join), but filtering the available records in the right table. In T-SQL would be like this:

    SELECT A.*
    FROM A
    LEFT JOIN B
        ON A.PrimaryKeyID = B.ForeignKeyID
        AND B.CriteriaField = @CriteriaValue
    WHERE B.ForeignKeyID IS NULL

    How can I translate that T-SQL query to a c# linq query?

    Any help will be very appreciated.


    Wednesday, January 8, 2014 7:03 AM

Answers

  • Hello,

    Being strange for the T-SQL and it should be like below:

    var result = from Invoices in db.Invoices
    
                                 join Discounts in db.Discounts on new { Invoices.InvoiceID, P1 = 1 } equals new { Discounts.InvoiceID, P1 = Discounts.CustomerID } into ID
    
                                 from IDs in ID.DefaultIfEmpty()
    
                                 where IDs.InvoiceID == null
    
                                 select new { Invoices, IDs };
    

    The generated T-SQL:

    SELECT 
    
    [Extent1].[InvoiceID] AS [InvoiceID], 
    
    [Extent1].[TotalAmount] AS [TotalAmount], 
    
    [Extent2].[InvoiceID] AS [InvoiceID1], 
    
    [Extent2].[CustomerID] AS [CustomerID], 
    
    [Extent2].[DiscountAmount] AS [DiscountAmount]
    
    FROM  [dbo].[Invoices] AS [Extent1]
    
    LEFT OUTER JOIN [dbo].[Discounts] AS [Extent2] ON ([Extent1].[InvoiceID] = [Extent2].[InvoiceID]) AND (1 = [Extent2].[CustomerID])
    
    WHERE [Extent2].[InvoiceID] IS NULL
    

    The result:

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Losphie Friday, January 10, 2014 4:50 PM
    Thursday, January 9, 2014 8:35 AM
    Moderator

All replies

  • I don't see how that result set can give any results, except the 1 case where primarykey of A is null.

    But here are some points you can follow.

    Create a real FK constraint between A and B, then A will get a property called Bs, that consist of a list of B
    And B will get a property A, that consist of the A that B points to.

    Now I am uncertain exactly what you want, but this would be a valid query:

    var query = from row in context.B
                select row.A;

    Wednesday, January 8, 2014 1:20 PM
  • Hi Losphie;

    Can you please check the T-SQL query you posted because I can not see how you are getting results with it. You are joining two tables A and B but the WHERE clause is stating that B does not exist?


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Wednesday, January 8, 2014 4:00 PM
  • First of all, thank's for your very quick responses...

    OK, may be I didn't explained me very clear using an abstract example. Here's a detailed example of what I want to achieve:

    DECLARE @Invoices TABLE (InvoiceID int, TotalAmount float)
    DECLARE @Discounts TABLE (InvoiceID int, CustomerID int, DiscountAmount float)
    
    INSERT INTO @Invoices VALUES (1, 100), (2, 200), (3,300)
    INSERT INTO @Discounts VALUES (1, 1, 10), (2, 2, 20)
    
    SELECT A.*
    FROM @Invoices A
    LEFT JOIN @Discounts B
    	ON A.InvoiceID = B.InvoiceID 
    	AND B.CustomerID=1
    WHERE B.InvoiceID IS NULL

    This is, get the invoices which doesn't have a discount, but only considering the customer with ID = 1. I know this doesn't make any sense in real life, but is only for illustrative purposes. 

    The result of this query executed in SSMS is:

    InvoiceID TotalAmount
    2 200
    3 300

    How can I achieve the same result using LinQ?

    Wednesday, January 8, 2014 6:03 PM
  • Hello,

    Being strange for the T-SQL and it should be like below:

    var result = from Invoices in db.Invoices
    
                                 join Discounts in db.Discounts on new { Invoices.InvoiceID, P1 = 1 } equals new { Discounts.InvoiceID, P1 = Discounts.CustomerID } into ID
    
                                 from IDs in ID.DefaultIfEmpty()
    
                                 where IDs.InvoiceID == null
    
                                 select new { Invoices, IDs };
    

    The generated T-SQL:

    SELECT 
    
    [Extent1].[InvoiceID] AS [InvoiceID], 
    
    [Extent1].[TotalAmount] AS [TotalAmount], 
    
    [Extent2].[InvoiceID] AS [InvoiceID1], 
    
    [Extent2].[CustomerID] AS [CustomerID], 
    
    [Extent2].[DiscountAmount] AS [DiscountAmount]
    
    FROM  [dbo].[Invoices] AS [Extent1]
    
    LEFT OUTER JOIN [dbo].[Discounts] AS [Extent2] ON ([Extent1].[InvoiceID] = [Extent2].[InvoiceID]) AND (1 = [Extent2].[CustomerID])
    
    WHERE [Extent2].[InvoiceID] IS NULL
    

    The result:

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Losphie Friday, January 10, 2014 4:50 PM
    Thursday, January 9, 2014 8:35 AM
    Moderator
  • I have not tested it, but this should work.

    var query = from row in context.Invoices
        where Invoices.Dicounts.Count()==0
        select row;

    Thursday, January 9, 2014 1:55 PM

  • Excellent! I'm very thankful! This solves the problem and clarifies other linq queries.

    Thank you so much!

    Friday, January 10, 2014 4:54 PM
  • I have not tested it, but this should work.

    var query = from row in context.Invoices
        where Invoices.Dicounts.Count()==0
        select row;

    Hi! I've tested the code, but even when I set the reverse member property in the dbml LINQ to SQL Class(Context) the Context.Invoices.Discounts object doesn't appear.

    Anyway, thank's for your reply.

    Friday, January 10, 2014 5:00 PM