-1
SELECT DISTINCT Title,
                ProductDescription,
                COUNT(1) as Duplicate
FROM DB_Deals
GROUP BY Title, ProductDescription
HAVING COUNT(1) > 1;
Erik Philips
  • 53,428
  • 11
  • 128
  • 150
Avinash Guleria
  • 7
  • 3
  • 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

1 Answers1

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

Community
  • 1
  • 1
jjj
  • 4,822
  • 1
  • 16
  • 39
  • 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