Solved

Changing this query into LINQ

Posted on 2009-07-10
13
201 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:brettr
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Closing Comment

by:brettr
Comment Utility
Yes - thanks.
0
 
LVL 29

Expert Comment

by:Göran Andersson
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need to Modify a Script I found 5 75
AutoHotkey ActiveX Component Error 8 55
Image(2) 3 21
Closing all open child forms. 4 22
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now