CODEWITHSUNDEEP
c#linq
gmang
gmang

Reputation: 435

LINQ Group by and having where clause

Below is the SQL Query I am trying to translate 

SELECT dbo.Contracts.Supplier 
FROM dbo.Contracts INNER JOIN dbo.Products ON dbo.Contracts.Product = dbo.Products.Product
where dbo.Products.ProductGroup='Crude'
GROUP BY dbo.Contracts.Supplier

Am I doing something wrong because I do not get same results with the following LINQ

     var result = from c in context.Contracts
                     join p in context.Products on c.Product equals p.Product1
                     where p.Product1.Equals("Crude")
                     group c by c.Supplier into g    
                     select new { supplier = g.Key  };

It is generating a weird statement 

SELECT 
1 AS [C1], 
[Distinct1].[Supplier] AS [Supplier]
FROM ( SELECT DISTINCT 
[Extent1].[Supplier] AS [Supplier]
FROM [dbo].[Contracts] AS [Extent1]
WHERE N'Crude' = [Extent1].[Product]
)  AS [Distinct1]

Using distinct would work but to get same results, LINQ should be generating a statement like so (it's like it is ignoring the join):

SELECT distinct dbo.Contracts.Supplier 
FROM dbo.Contracts INNER JOIN dbo.Products ON dbo.Contracts.Product = dbo.Products.Product
where dbo.Products.ProductGroup='Crude'

Upvotes: 3

Views: 4041

Answers (1)

Jarek
Jarek

Reputation: 3379

I'm assuming that you are using 'EntityFramework' or 'Linq To SQL'. If so, you should be able to use navigation properties to navigate to product and filter invalit results out. This way your query might look something like this:

var result = (from c in context.Contracts
              where c.Products.Any(p => p.ProductGroup == "Crude")
              select c.Supplier).Distinct();

It will automatically convert into correct query (in this case possibly without join even, just using Exists sql keyword) and return distinct suppliers. This is if I understand your objective correctly - you want to obtain all suppliers assigned to contracts that contain product from 'Crude' product group.

Basically you should try to avoid using joins from linq to sql or linq to entities as much as possible when you can use navigation properties. System will probably be better at converting them into specific sql.

Upvotes: 1

Related Questions