?
Solved

Changing this query into LINQ

Posted on 2009-07-10
13
Medium Priority
?
207 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 2000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

801 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