Please help me convert a Linq Query to a SQL Query... Desperate...

Question

Hey all,

Im pretty proficient in LINQ, but not in SQL. I understand cursors are horrible and shouldn't be used. I know SQL Syntax pretty well, but I am trying to figure out how to convert this query and update to SQL from Linq. I don't know how to go through the Foreach of SQL without using cursors and Im a bit lost on what to do next..

As you can see, I am going through an entire table of about 150,000 rows Linqpad just cannot handle the update, so I need this to be done in SQL.

Im iterating through each record in the first table, then finding a Guid in two other tables and pulling that data from those two tables and updating the original.

Can anyone help? Thanks ALL!!!!!

  CS_Code.UtopiaDataContext db = new CS_Code.UtopiaDataContext();
        var getFirst = (from xx in db.Utopia_Province_Data_Captured_Gens
                        select xx);
        foreach (var item in getFirst)
        {
            var updateItem = (from xx in db.Utopia_Province_Infos
                              where xx.Province_ID == item.Province_ID
                              select xx).FirstOrDefault();
            if (updateItem != null)
            {
                item.Owner_User_ID = updateItem.User_ID;
                item.Last_Login_For_Province = updateItem.Last_Login_Province;
                item.Date_Time_User_ID_Linked = updateItem.Date_Time_Added;
                item.Added_By_User_ID = updateItem.Added_By_User_ID;
            }
            var updateItema = (from xx in db.Utopia_Province_Identifiers
                               where xx.Province_ID == item.Province_ID
                               select xx).FirstOrDefault();
            if (updateItema != null)
            {
                item.Owner_Kingdom_ID = updateItema.Owner_Kingdom_ID;
                item.Kingdom_ID = updateItema.Kingdom_ID;
                item.Province_Name = updateItema.Province_Name;
                item.Kingdom_Island = updateItema.Kingdom_Island;
                item.Kingdom_Location = updateItema.Kingdom_Location;
            }
        }
        db.SubmitChanges();

Answer 1

Well, you should be doing a join in the first place - currently you're executing two extra queries for each row of the table, which is incredibly inefficient. Here's an example of the join:

var results = from xx in db.Utopia_Province_Data_Captured_Gens
              join yy in db.Utopia_Province_Infos 
                       on xx.Province_ID equals yy.Province_ID
              select new { item = xx, updateItem = yy };
foreach (var result in results)
{
    result.item.Owner_User_ID = result.updateItem.User_ID;
    result.item.Last_Login_For_Province = result.updateItem.Last_Login_Province;
    result.item.Date_Time_User_ID_Linked = result.updateItem.Date_Time_Added;
    result.item.Added_By_User_ID = result.updateItem.Added_By_User_ID;
}

// Ditto for second query

Note that this will update all items with matching Province_IDs rather than just the first one, but I would guess that these are the primary keys anyway, so it won't be a problem.

EDIT: I should note that Asaf's solution is a preferable in terms of efficiency. There's no point in fetching all the data back to the client when the database can do it all itself.

Answer 2

If I understand correctly, you're trying to do an update query. First, if you can use Jon Skeet's suggestion and you're more comfortable with LINQ, then go for it. The SQL equivalent should be something like -

UPDATE info
SET
  gens.Owner_User_ID = item.User_ID
  gens.Last_Login_For_Province = item.Last_Login_Province
FROM
  Utopia_Province_Infos as info 
  INNER JOIN Utopia_Province_Data_Captured_Gens as gens 
  ON info.Province_ID = gens.Province_ID

This query joins two tables, making each row a "long" one that contains both tables. It proceeds to update some of the fields in each row.

You set the rest of Utopia_Province_Data_Captured_Gens's fields the same way as with User_ID. You do the same replacing Utopia_Province_Infos with Utopia_Province_Identifiers for the second table in your code.

Note: I did not take into account your usage of FirstOrDefault. You can set default value directly in Utopia_Province_Infos, or simply update values that have not been set (using a where clause). About the 'First' - is there more than one row with the same Province_ID in Utopia_Province_Infos? Why are you going for the first?