Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Convert SQL to LINQ

Posted on 2010-01-13
21
Medium Priority
?
1,362 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
  • 9
  • 9
  • 3
21 Comments
 
LVL 42

Expert Comment

by:Meir Rivkin
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:Meir Rivkin
ID: 26305164
u need to add System.Linq reference
0
Industry Leaders: 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!

 
LVL 42

Expert Comment

by:Meir Rivkin
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

Industry Leaders: 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

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
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

580 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