We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Changing this query into LINQ

brettr
brettr asked
on
Medium Priority
220 Views
Last Modified: 2013-11-11
How can I change the last SQL query here into C# LINQ code: https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24561436.html#a24827701?  It's the last query listed ( ID: 24827977)
Comment
Watch Question

CERTIFIED EXPERT

Commented:
You wouldn't convert that query to LINQ, you would do it in a different way.

Like:

IEnumerable<int> persons =
   locks
   .GroupBy(x => x.PersonId)
   .Where(g => g.Count()%2 == 1)
   .Select(g => g.First().PersonId);

Open in new window

Author

Commented:
That doesn't even look close to what I want.  What exactly are you trying to get?

Also, what is "locks"?
CERTIFIED EXPERT

Commented:
It groups the locks on the person id, and returns the id of the persons that have an odd number of locks.

The locks variable is a list of Lock objects that I created from your example data.

If it's not even close to what you want, you have to specify what it is that you want... It does return exactly what you wanted from the test data that you provided.

Author

Commented:
Ok.  I continue to get the error:

The name 'locks' does not exist in the current context      

Where are you defining it?

Author

Commented:
I've got it working.  But it isn't what I want.  This brings back everything.  Did you even run the query?
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Yes - thanks.
CERTIFIED EXPERT

Commented:
What is the result that you are getting?

I ran the query, and it gives exactly the result that you asked for.

Author

Commented:
@jinal:

What does the "@" mean in
   
   where p.@lock

Why not just do

  where p.lock


@GreenGhost:

Check my response in  ID: 24828789.
CERTIFIED EXPERT

Commented:
I don't understand how you possibly could get everything back. To start with, the query groups on the PersonId, so there is only one group per person. As it only returns one object from each group, the result can't have more than one object per person.

Can you show how you use the code?

(If you can get the grouping to work it's more efficient that running a query in the condition of another query.)

Commented:
Hello ,

@locak is just field . It could be normal field and i just gave name like that. You have locked field in your example i think so ... it is just normal.

Author

Commented:
@GreenGhost:

Sorry - not everything but a unique list of persons whether they were locked or not.  The conditions are needed to avoid that.
CERTIFIED EXPERT

Commented:
Did I misunderstand the data? The first record is a lock, and the second record removes it, so if a person has an even number of records, the lock is removed?
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.