How can I convert a sql query to Linq query?

Question

SELECT DISTINCT Title,
                ProductDescription,
                COUNT(1) as Duplicate
FROM DB_Deals
GROUP BY Title, ProductDescription
HAVING COUNT(1) > 1;

Just a minor comment, no need for DISTINCT since your GROUP BY doesn't return any duplicates. Also you can replace the select list's COUNT(1) with just 1, or perhaps you want count(*)? — jarlh, May 04 '15 at 12:49
it is in sql how can i change that in mvc Ef ? yes i want all the same record . — Avinash Guleria, May 04 '15 at 13:01
Your question has nothing to do with MVC (The asp.net-mvc framework has no code that has anything to do with databases). Secondly, when you write a query you can use LINQ or LAMBDA. These queries work against different types of datasources (internal memory, XML, Linq2Sql and Entity Framework to name a few). So specifically converting this TSQL also has nothing to do with Entity Framework specifically. — Erik Philips, May 04 '15 at 18:21
@ErikPhilips: I don't see the TSQL here -- it looks like it could be standard SQL. Also, the fact that the author mentioned Entity Framework could be important, since there's a difference between LINQ to SQL and LINQ to Entities. — jjj, May 04 '15 at 20:10

score 0 · Accepted Answer · edited May 23 '17 at 12:06

0

Well, if by EF, you mean making a query using LINQ to Entities...

from deal in context.DB_Deals
group deal by new { deal.Title, deal.ProductDescription } into dealGroup
where dealGroup.Count() > 1
select new {
    dealGroup.Key.Title,
    dealGroup.Key.ProductDescription,
    Duplicate = dealGroup.Count(),
}

Assuming, context is your DbContext, and DB_Deals is your mapped table name.

See

edited May 23 '17 at 12:06

Community

1
1

answered May 04 '15 at 18:17

jjj

4,602
13
37

select a.Title,a.ProductDescription,a.VendorName from DB_Deals a join (SELECT DISTINCT Title, ProductDescription, COUNT(1) as Duplicate FROM DB_Deals GROUP BY Title, ProductDescription HAVING COUNT(1) > 1)b on a.Title=b.Title and a.ProductDescription=b.ProductDescription – Avinash Guleria May 05 '15 at 09:09
anbody can help me how can i convert my sql querry to MVC Ef? – Avinash Guleria May 05 '15 at 09:10
First, if you're going to ask another question, it's usually a good idea to create a new post, because there aren't that many people who will see new comments to an answer. – jjj May 05 '15 at 17:31
As for using LINQ to generate a similar query, maybe this would help: https://msdn.microsoft.com/en-us/library/bb397676.aspx – jjj May 05 '15 at 17:31

How can I convert a sql query to Linq query?

1 Answers1