Click here to Skip to main content
Rate this: bad
good
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
   where 
      (c.Agency.Equals(Agency))
   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:
SELECT
   DISTINCT   division
FROM tEmp
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?
Thanks!
 
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.
 
P.S.
Updated sql statement to
WHERE   Agency= @Agency
(was: division=@Agency)
Posted 23-Aug-12 11:17am
Edited 27-Aug-12 6:51am
v3
Rate this: bad
good
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
SELECT DISTINCT division
FROM tEmp
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.
  Permalink  
Rate this: bad
good
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.
  Permalink  
Comments
jccompton43 at 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 at 27-Aug-12 12:30pm
   
You can run stored procs from EF. Perhaps that is what you need.
jccompton43 at 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
good
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 http://social.msdn.microsoft.com/forums/en-US/linqprojectgeneral/thread/87adac06-c590-4b6f-93a0-f352d397eb01/[^]
 
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.
  Permalink  
v2
Rate this: bad
good
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?
  Permalink  
v2
Comments
jccompton43 at 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 at 27-Aug-12 13:54pm
   
So, in that case, see the updated solution above. Is that what you want?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

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

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



Advertise | Privacy | Mobile
Web03 | 2.8.140926.1 | Last Updated 27 Aug 2012
Copyright © CodeProject, 1999-2014
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