Changing this query into LINQ

How can I change the last SQL query here into C# LINQ code: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24561436.html#a24827701?  It's the last query listed ( ID: 24827977)
brettrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Göran AnderssonCommented:
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

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

Also, what is "locks"?
0
Göran AnderssonCommented:
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.
0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

brettrAuthor Commented:
Ok.  I continue to get the error:

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

Where are you defining it?
0
brettrAuthor Commented:
I've got it working.  But it isn't what I want.  This brings back everything.  Did you even run the query?
0
jinalCommented:
Please try this.

 var data = from p in con.TestTables
                       where p.@lock == true && ((from pp in con.TestTables where pp.personid == p.personid && pp.@lock == false && pp.date >= p.date select pp).Count() == 0)
                       select p.personid;

 var data = from p in con.TestTables
                       where p.@lock == true && ((from pp in con.TestTables where pp.personid == p.personid && pp.@lock == false && pp.date >= p.date select pp).Count() == 0)
                       select p.personid;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brettrAuthor Commented:
Yes - thanks.
0
Göran AnderssonCommented:
What is the result that you are getting?

I ran the query, and it gives exactly the result that you asked for.
0
brettrAuthor Commented:
@jinal:

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

Why not just do

  where p.lock


@GreenGhost:

Check my response in  ID: 24828789.
0
Göran AnderssonCommented:
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.)
0
jinalCommented:
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.
0
brettrAuthor Commented:
@GreenGhost:

Sorry - not everything but a unique list of persons whether they were locked or not.  The conditions are needed to avoid that.
0
Göran AnderssonCommented:
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?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.