Solved

Changing this query into LINQ

Posted on 2009-07-10
13
205 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 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