Solved

Changing this query into LINQ

Posted on 2009-07-10
13
204 Views
Last Modified: 2013-11-11
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)
0
Comment
Question by:brettr
  • 6
  • 5
  • 2
13 Comments
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 24828444
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
 

Author Comment

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

Also, what is "locks"?
0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 24828507
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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 

Author Comment

by:brettr
ID: 24828764
Ok.  I continue to get the error:

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

Where are you defining it?
0
 

Author Comment

by:brettr
ID: 24828789
I've got it working.  But it isn't what I want.  This brings back everything.  Did you even run the query?
0
 
LVL 15

Accepted Solution

by:
jinal earned 500 total points
ID: 24828808
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
 

Author Closing Comment

by:brettr
ID: 31602328
Yes - thanks.
0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 24830388
What is the result that you are getting?

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

Author Comment

by:brettr
ID: 24834790
@jinal:

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

Why not just do

  where p.lock


@GreenGhost:

Check my response in  ID: 24828789.
0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 24834977
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
 
LVL 15

Expert Comment

by:jinal
ID: 24834986
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
 

Author Comment

by:brettr
ID: 24835014
@GreenGhost:

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

Expert Comment

by:Göran Andersson
ID: 24842178
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

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question