the nature of the SELECT query in MVC and LINQ TO SQL

advertisements

i am bit confused by the nature and working of query , I tried to access database which contains each name more than once having same EMPid so when i accessed it in my DROP DOWN LIST then same repetition was in there too so i tried to remove repetition by putting DISTINCT in query but that didn't work but later i modified it another way and that worked but WHY THAT WORKED, I DON'T UNDERSTAND ?

QUERY THAT DIDN'T WORK

 var names = (from n in DataContext.EmployeeAtds select n).Distinct();

QUERY THAT WORKED of which i don't know how ?

var names = (from n in DataContext.EmployeeAtds select new {n.EmplID, n.EmplName}).Distinct();

why 2nd worked exactly like i wanted (picking each name 1 time)

i'm using mvc 3 and linq to sql and i am newbie.


Both queries are different. I am explaining you both query in SQL that will help you in understanding both queries.

Your first query is:

   var names = (from n in DataContext.EmployeeAtds select n).Distinct();

SQL:-

SELECT DISTINCT [t0].[EmplID], [t0].[EmplName], [t0].[Dept] FROM [EmployeeAtd] AS [t0]

Your second query is:

 (from n in EmployeeAtds select new {n.EmplID, n.EmplName}).Distinct()

SQL:-

SELECT DISTINCT [t0].[EmplID], [t0].[EmplName] FROM [EmployeeAtd] AS [t0]

Now you can see SQL query for both queries. First query is showing that you are implementing Distinct on all columns of table but in second query you are implementing distinct only on required columns so it is giving you desired result.