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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Prabhakaran Soundarapandian · Answer 1 · 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

pramod.hegde · Answer 2 · 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.

Matt T Heffron · Answer 3 · 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?

Clifford Nelson · Answer 4 · 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.

Christian Graus · Answer 5 · 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 :-)

I just need the distinct results

5 solutions

Solution 5

Solution 4

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0