I just need the distinct results

Question

4.00/5 (1 vote)

See more:

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:

SQL

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:

XML

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

JChrisCompton

Updated 27-Aug-12 6:51am

v3

Add a Solution

5 solutions

Add a Solution

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

pramod.hegde · Answer 1 · 2012-08-23T18:59:00

Try this,

C#

string Agency = "";
var query = (from c in context.tEmp
             where c.Agency.Equals(Agency)
             select c.Division).Distinct();

This is equivalent to

SQL

SELECT DISTINCT division
FROM tEmp
WHERE division= @Agency

But query statement would execute only when you use it, for example,

C#

query.Count() // when you call this statement then the query gets executed.

Christian Graus · Answer 2 · 2012-08-23T11:29:00

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.

Posted 23-Aug-12 11:29am

Christian Graus

Comments

JChrisCompton 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.

JChrisCompton 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 :-)

Clifford Nelson · Answer 3 · 2012-08-23T12:29:00

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:

C#

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.

Matt T Heffron · Answer 4 · 2012-08-23T15:01:00

With the following "dummy" data:

C#

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:

C#

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:

C#

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?

Prabhakaran Soundarapandian · Answer 5 · 2012-08-23T20:46:00

Solution 5

try this

C#

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

Posted 23-Aug-12 20:46pm

Prabhakaran Soundarapandian

I just need the distinct results

5 solutions

Solution 4

Solution 1

Solution 2

Solution 3

Solution 5

Add your solution here

Preview 0

I just need the distinct results

5 solutions

Solution 4

Solution 1

Solution 2

Solution 3

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us