Click here to Skip to main content
12,290,716 members (30,004 online)
Rate this:
Please Sign up or sign in to vote.
See more: C# EF4.0
I need to convert this to a distinct query. So far all efforts have failed
string Agency
var query = (
   from c in context.tEmp
   orderby c.Division
   select new
      OrgName = c.Division

This returns each division multiple times, I only want the distinct list of divisions.
In SQL Server it would be this:
   DISTINCT   division
WHERE   Agency= @Agency

I've tried var query = ( … ) .distinct and various other things like a second var queryDistinct = ( … from c in query …);

I know there's a simple way, can anyone help?

Edit 8/27/2012 - Quick clarification: (and over-simplification)

I have three web service calls to write:
public List<Org> GetAgencies()
public List<Org> GetDivisionsInAgency(string Agency)
public List<Emp> EmployeeSearchByNameAgencyDivision(string EmpName, string Agency, string Division)

So, yes, in the query I do want to return the distinct Divisions (within the given agency).
Why am I doing this? Because I don't control the input data, so the division names (and agencies/department/group/section/branch) can change at any point in time. By doing this I hope that anyone consuming the service will be able to debug why they can no longer get a list of employees in their agency/division without calling me (whether they will or not is a different story).

Does this help? I guess I simplified it too much (looks like a mistake in the question)

Thanks for the answers so far, I'm starting to work through them and will mark which ones work.
-Chris C.

Updated sql statement to
WHERE   Agency= @Agency
(was: division=@Agency)
Posted 23-Aug-12 11:17am
Edited 27-Aug-12 6:51am
Rate this: bad
Please Sign up or sign in to vote.

Solution 4

Try this,
string Agency = "";
var query = (from c in context.tEmp
             where c.Agency.Equals(Agency)
             select c.Division).Distinct();
This is equivalent to
WHERE division= @Agency

But query statement would execute only when you use it, for example,
query.Count() // when you call this statement then the query gets executed.
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

context.tEmp.Where(p => p.Agency == Agency).Distinct(); is how I'd do it, assuming the first bit is right ( I never use the syntax you're using ).

Of course, Agency needs to have a value, to search and you're getting a full entity list, not an anonymous class with just the one property.
jccompton43 27-Aug-12 12:25pm
This would probably work if those were the only two fields in the table, but it is returning a division name for each row that matches pAgency==Agency.

I should have specified that there are multiple people in each division.
Is there anyone listening that can tell me how to change the above to SELECT DISTINCT division FROM tEmp ?
Christian Graus 27-Aug-12 12:30pm
You can run stored procs from EF. Perhaps that is what you need.
jccompton43 27-Aug-12 13:14pm
Yeah, maybe... I'm too close to my deadline to mess with it much more. I just don't know enough (or I'm not practiced enough) to make much headway - a little knowledge is a dangerous thing :-)
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

What you need to do is create a list of the names, which are strings. That gives you something to have that the distinct will work on. If you do a distinct on some object that contains the name, then you will get everything unless you overload the .

var divisionNames = divisions.Select(d => d.Name).Distinct();

You can read about using an Equity comparer at[^]

Here is a little code you can run to test what I am saying in debugging:

public class test
    public static void run()
        var testgroup1 = new[]
                                new Class1() {Value = "a"},
                                new Class1() {Value = "a"},
                                new Class1() {Value = "b"},
        var result1 = testgroup1.Distinct();
        var result3 = testgroup1.Select(i => i.Value).Distinct();
public class Class1
    public string Value { get; set; }
    public override int GetHashCode()
        return Value.GetHashCode();

Another option is using the GroupBy, but that is a bit more complex.
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

With the following "dummy" data:
var coll = new[] {
  new {Agency = "AA", Div="a", Val=0},
  new {Agency = "BB", Div="a", Val=-1},
  new {Agency = "BB", Div="b", Val=-2},
  new {Agency = "BB", Div="b", Val=-3},
  new {Agency = "AA", Div="a", Val=-4},
  new {Agency = "AA", Div="c", Val=-5},
  new {Agency = "AA", Div="d", Val=-6},
  new {Agency = "CC", Div="a", Val=-7},
If you want the collection of distinct division values, try something like this:
string Agency = "AA";
var q = from x in coll
        where x.Agency == Agency 
        group x by x.Div into dg 
        select new { Div = dg.Key, Count = dg.Count() };
Giving 3 results:
q[0] = { Div = "a", Count = 2 }
q[1] = { Div = "c", Count = 1 }
q[2] = { Div = "d", Count = 1 }
Is this what you're looking for?
jccompton43 27-Aug-12 12:59pm
Oops. Duh. Corrected in original question.
I meant to say: WHERE Agency=@Agency

I changed the source code to prevent everyone from knowing my schema (because that's the rule) and mispasted that field when I was recreating the SQL.
Matt T Heffron 27-Aug-12 13:54pm
So, in that case, see the updated solution above. Is that what you want?
Rate this: bad
Please Sign up or sign in to vote.

Solution 5

try this
IList<string> OrgName = context.tEmp.Where(p => p.Agency.Tolower() == AgencyTolower())

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.160518.1 | Last Updated 27 Aug 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100