[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Changing this query into LINQ

Posted on 2009-07-10
13
Medium Priority
?
208 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

656 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