Quick Help: converting SQL to LINQ

Question

How do you convert this SQL to LINQ?

I'm reading it now, but just putting this out there in case I can't do it.

        SqlConnection connection = new SqlConnection
        {
            ConnectionString = ConfigurationManager.ConnectionStrings["HBOS"].ConnectionString
        };

        connection.Open();

        foreach (ExchangeRateData x in exchangeRateDatas.ExchangeRateDataList)
        {
            SqlCommand cmd = new SqlCommand("UPDATE dbo.CurrencyExchange " +
                                            "SET Rate = '" + x.Rate + "', DateTimeStamp = CAST('" + x.TimeStamp +
                                            "' AS DATETIME), CreatedBy = '" + x.CreatedBy + "', RateInv = '" +
                                            x.RateInv + "' " +
                                            "WHERE Currency = '" + x.ToCurrency + "';", connection);
            // Sql query and connection
            cmd.ExecuteNonQuery();
        }

        connection.Close();

Linq is for _querying_ not _updating_. If you want to update data you can continue to use ADO like you do, or use another ORM like Entity Framework. If you continue to use ADO I would switch to using parameters instead of concatenating SQL. — D Stanley, Apr 22 '14 at 15:57
But my boss says this is the old way of doing it. Is there a LINQ way to do the same thing? He just wants LINQ everything. — NoName, Apr 22 '14 at 16:00
then your boss doesn't understand what Linq is for. Linq has no mechanism to update data - only to query it. — D Stanley, Apr 22 '14 at 16:06
Pull your object out first, then update the objects by setting their property values, then save your context. As @DStanley mentions tho, you need an ORM like EF to really accomplish this. — crthompson, Apr 22 '14 at 16:07
@DStanley Not true - you are referring only to the Linq querying language, but there is an entire API built around it connecting to SQL for CRUD operations - which seems to be what the OP is getting at. — McCee, Apr 22 '14 at 16:18
If you're referring to Entity Framework, that is independent of Linq. Updating data in EF does not use Linq at all. _Querying_ data through EF is easy with Linq, but they are orthogonal technologies. — D Stanley, Apr 22 '14 at 16:23
EF is very different than LINQ to SQL. They have basic similarities, (CRUD, modeling, etc.), but each have their own limitations. — McCee, Apr 22 '14 at 16:24
http://stackoverflow.com/questions/8676/entity-framework-vs-linq-to-sql — McCee, Apr 22 '14 at 16:25

score 3 · Accepted Answer · edited Apr 22 '14 at 17:15

3

Create a dbcontext first

then

CurrencyExchange CurrencyExchangeObject = context.CurrencyExchange
                                         .Where(a => a.Currency = x.ToCurrency)
                                         .FirstOrDefault();

after that you can simple assign the values like

CurrencyExchangeObject.Rate = x.Rate;
CurrencyExchangeObject.DateTimeStamp  = Convert.ToDateTime(x.TimeStamp);

and then simply say

context.SaveChanges();

edited Apr 22 '14 at 17:15

Habib

219,104
29
407
436

answered Apr 22 '14 at 16:13

Karthik Ganesan

4,142
2
26
42

Okay, I guess I have to set up EF first. – NoName Apr 22 '14 at 17:57

score 0 · Answer 2 · answered Apr 22 '14 at 16:13

Sounds like your boss is looking for a LINQ to SQL implementation. Unfortunately, your question does not have a quick answer because adding this functionality requires a lot more than just "converting a query to LINQ", as there are a number of things needed to get your environment set up to support it.

You may want to start with some basic Googling of the topic:

First couple results:

http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx http://msdn.microsoft.com/en-us/library/bb386976(v=vs.110).aspx

LINQ to SQL has a more widely-used cousin called Entity Framework, which is not dependent upon SQL Server. You may want to consider that as well.

Quick Help: converting SQL to LINQ

2 Answers2