?
Solved

Convert SQL to LINQ

Posted on 2010-01-13
21
Medium Priority
?
1,324 Views
Last Modified: 2013-11-11
I have a simple sql statement on a single table that works fine, but I am unable to convert it into a LINQ statement:
The SQL is

SELECT *
      FROM CurrencyExchange
      WHERE ce_date = (
            SELECT MAX(ce_date)
            FROM CurrencyExchange a
            WHERE ce_date <= GETDATE()
            AND CurrencyExchange.ce_cy_from_id = a.ce_cy_from_id
            AND CurrencyExchange.ce_cy_to_id = a.ce_cy_to_id
            )
            order by ce_cy_from_id, ce_cy_to_id


this outputs the CurrencyExchange table

ce_id ce_date ce_cy_from_id ce_cy_to_id ce_rate


What is the equivalent LINQ code in C# to achieve this?

0
Comment
Question by:declanmcd
[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
  • 9
  • 9
  • 3
21 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 26304786
try:

var result = CurrencyExchange.Where(n => n.ce_date == table.Select(e => e.ce_date).Max() &&
                n.ce_date <= DateTime.Now).OrderBy(n => n.ce_cy_from_id).ThenBy(n => n.ce_cy_to_id);
0
 
LVL 3

Author Comment

by:declanmcd
ID: 26305070
@sedgwick

That doesn't work as written (the table.Select is not recognised) so I changed it to CurrencyExchange.Select assuming that was what you meant?

Anyway it still doesn't work. I already had something similar and all it does is return a list of the values in the table that have the Max date. That is not what I was after.
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 26305164
u need to add System.Linq reference
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 42

Expert Comment

by:sedgwick
ID: 26305169
and replace table with CurrencyExchange.
0
 
LVL 3

Author Comment

by:declanmcd
ID: 26305202
System.Linq reference is already there.

But in your original syntax I get (as expected)

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

Which is why I changed it to CurrencyExchange.Select.
If this is not correct then tell me what it should be.
0
 
LVL 3

Author Comment

by:declanmcd
ID: 26305469
As I siad it doesn't return what I want, only a list of the items in the table with the Maximum ce_date.


For example there are multiple records for each currency, all with different effective dates, and the join needs to be on the latest record (max date)
0
 
LVL 4

Expert Comment

by:DaveJellison
ID: 26312499
It actually looks remarkably like SQL itself...

Keep this link in your bookmarks... http://odetocode.com/blogs/scott/archive/2007/09/24/nested-selects-in-linq-to-sql.aspx


var table = new ExpertExchangeDataSet.CurrencyExchangeDataTable();
			currencyExchangeTableAdapter.Fill(table);
			var query = from row in table
			            orderby row.ce_cy_from_id , row.ce_cy_to_id
			            select new {
			                       	id = row.id,
			                       	ce_rate = row.ce_rate,
			                       	ce_cy_from_id = row.ce_cy_from_id,
			                       	ce_cy_to_id = row.ce_cy_to_id,
			                       	ce_date = (DateTime?) (from row2 in table where row.ce_cy_from_id == row2.ce_cy_from_id && row.ce_cy_to_id == row2.ce_cy_to_id && row2.ce_date <= DateTime.Now select row2.ce_date).Max()
			                       };

			foreach (var result in query)
			{
				Debug.WriteLine(result.id);
				Debug.WriteLine(result.ce_date);
				Debug.WriteLine(result.ce_cy_from_id);
				Debug.WriteLine(result.ce_cy_to_id);
			}

Open in new window

0
 
LVL 3

Author Comment

by:declanmcd
ID: 26312940
@DaveJellison

Nice try Dave, but it doesn't work. That just ends up returning all the records in the table.

Here is the SQL generated via your LINQ:

exec sp_executesql N'SELECT [t0].[ce_id] AS [id], [t0].[ce_rate], [t0].[ce_cy_from_id], [t0].[ce_cy_to_id], (
    SELECT MAX([t1].[ce_date])
    FROM [dbo].[CurrencyExchange] AS [t1]
    WHERE ([t0].[ce_cy_from_id] = [t1].[ce_cy_from_id]) AND ([t0].[ce_cy_to_id] = [t1].[ce_cy_to_id]) AND ([t1].[ce_date] <= @p0)
    ) AS [ce_date]
FROM [dbo].[CurrencyExchange] AS [t0]
ORDER BY [t0].[ce_cy_from_id], [t0].[ce_cy_to_id]', N'@p0 datetime', @p0 = 'Jan 14 2010 11:41:53:263AM'
0
 
LVL 4

Expert Comment

by:DaveJellison
ID: 26319488
I misunderstood then. I thought you wanted all matching records with the Max record set as the date. You'll notice what I supplied doesn't just "return all record" as the ce_date field will be the max value of the "row2" ce_date field, not just the ce_date field of the "row" selected by the initial (outer) select. If all you want is the first result based on your order by statement, simply stick a FirstOrDefault() an the very end of the entire LINQ statement. That will produce a single "row" result and should generate the proper SQL you're looking for and not interrogate the database for all rows. By that I mean you not only should get a single row back but you won't incur the cost of returning a potentially large record set every time you perform your Linq query.

Please let me know if I'm still missing what you're attempting to accomplish. If so, please provide some sample screenshots of your data in MSSQLMS or a similar tool if possible with an additional snapshot of a sample result set using your SQL query itself so that I, or other experts, may better address your question.
0
 
LVL 4

Expert Comment

by:DaveJellison
ID: 26319497
Side Note: if you put the FirstOrDefault call on your Linq and you determine the generated SQL is pulling back a set optionally try the Linq Take(1) method. Actually, I think that's the preferred Linq/SQL methodology and I should have suggested that first. My apologies.
0
 
LVL 3

Author Comment

by:declanmcd
ID: 26321311
@DaveJellison:

I'm afraid that won't do it either. Basically the table lists records of currency exchange rates and the date they applied. For example on 1 Jan 2009 there may have been a record to exchange Euros to Dollars at a particular rate, so there willl be a record for EUR-USD on 1 Jan 2009.
The next day that rate will have changed and so there will be a second record for EUR-USD with 2 Jan 2009.
My query returns all the latest exchange rates, i.e. the last occurrence of EUR-USD) for all exchanges. So there will be records for EUR-USD, EUR-GBP, GBP-USD etc.
At the moment there are about 20k records and the SQL above return the hundred or so records perfectly.

The way I do it at the moment is to create a view with the SQL above and use LINQ on that view, but obviously I would like to cut out the view and use LINQ directly.

0
 
LVL 4

Expert Comment

by:DaveJellison
ID: 26321327
Have you looked into Linq grouping and tried that approach already?

http://msdn.microsoft.com/en-us/vcsharp/aa336754.aspx#simple1
0
 
LVL 4

Accepted Solution

by:
DaveJellison earned 1000 total points
ID: 26322079
OK I ran your original query against a fake database I created.

Using the following I get the same results as the original query based on the quess-work data I put into the table...


from t in db.CurrencyExchange
where
  t.Ce_date == 
	(from a in db.CurrencyExchange
	where
	  a.Ce_date <= DateTime.Now &&
	  t.Ce_cy_from_id == a.Ce_cy_from_id &&
	  t.Ce_cy_to_id == a.Ce_cy_to_id
	select new {
	  a.Ce_date
	}).Max(p => p.Ce_date)
orderby
  t.Ce_cy_from_id,
  t.Ce_cy_to_id
select new {
  t.Id,
  t.Ce_date,
  t.Ce_cy_from_id,
  t.Ce_cy_to_id,
  t.Ce_rate
}

Open in new window

0
 
LVL 3

Author Comment

by:declanmcd
ID: 26322124
@DaveJellison:

Yes I'm aware of Linq grouping, but as to how it applies to my scenario is the key. And what would be the outcome of using it?
0
 
LVL 4

Expert Comment

by:DaveJellison
ID: 26322152
I'm not sure if my last post was available before you posted your grouping question. Please advise. (although I ended up not using grouping)
0
 
LVL 3

Author Comment

by:declanmcd
ID: 26324028
@DaveJellison:

sorry didn't see your code post.

But yes it certainly seems to do the trick. I'm comparing the returned values with the SQL values and so far so good. The only downside is that the linq query takes nearly 6 times longer compared to the native sql.
LinqPad query is 0.9 sec for linq compared with 0.15 sec for sql, but this is to be expected.
0
 
LVL 3

Author Closing Comment

by:declanmcd
ID: 31676675
Spot on, well done and thank a million!
0
 
LVL 4

Expert Comment

by:DaveJellison
ID: 26325985
Glad I could help (finally lol). I don't know what flavor of DAL you're using but Entity Framework 4 (shipping with VS 2010) has first class support for stored procedures. If this particular SQL query is mission-critical when it comes to performance, you'll be able to keep it inside a stored proc and do some very Linq'ish type manipulation of the results. VS 2010 is slated for March release I believe. Best of luck on your projects.
0
 
LVL 4

Expert Comment

by:DaveJellison
ID: 26326034
Oh, I almost forgot. Check out this tool as it helped me in answering your question. This will convert SQL statements to Linq statement provided a working dataset and some Linq-to-Sql code (that it will generate for you)

http://www.sqltolinq.com/

Might save you some time and energy in the future. Hope it helps.
0
 
LVL 3

Author Comment

by:declanmcd
ID: 26326096
Thanks. I was contemplating using Linqer but I thought I'd try EE first as it was cheaper :)
0
 
LVL 4

Expert Comment

by:DaveJellison
ID: 26326118
Touche :)
0

Featured Post

Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Suggested Courses

770 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