Translating Linq Code Block into SQL

Question

The main goal is to migrate the appropriate data from one table to another, and since my skills are not sharp in sql, I wrote the logic using linq and foreach statement to accomplish what is needed. However, a sql script is needed to run in different environment, so I started to translate what I previously wrote into sql, and boom my sql thought process was left broken at the GroupBy and looping shores.

Here is my attempt which in fact returns an accurate data result:

SELECT BookID FROM (SELECT bt.BookID, pt.PaperID, ppt.PropID
FROM BooksTable AS bt
JOIN PapersTable AS pt
ON bt.BookID = pt.BookID
JOIN PapersPropertiesTable AS ppt
ON pt.PaperID = ppt.PaperID) AS ResultSet
GROUP BY BookID

Here is the code where I hit a rock:

foreach (var i in ResultSetGroupedByBookID)
{
    var resultToMigrate= i.DistinctBy(j => j.PropID).ToList();

    foreach (var j in resultToMigrate)
    {
        db.InsertPropsProcedure(j.BookID, j.PropID);
    }
}

The major challenges:

GroupingBy that returns an inner result set with other columns included
Loop through the inner result set, DistinctBy PropID and return another set
Loop through the new set, call the insert procedure and pass the params

Update:

I selected Stepen's answer for its simplicty; however, I ran into the scenario where I needed to Distinct By multiple columns while returning a third, so I had to do the following modifications:

INSERT INTO Table2(BookID, PropID, PropValue)
SELECT DISTINCT bt.BookID, ppt.PropID, MAX(ppt.PropValue) AS PropValue
FROM BooksTable AS bt
JOIN PapersTable AS pt ON bt.BookID = pt.BookID
JOIN PapersPropertiesTable AS ppt ON pt.PaperID = ppt.PaperID
GROUP BY bt.BookID, ppt.PropertyID
ORDER BY bt.BookID

score 2 · Answer 1 · answered Oct 20 '17 at 19:09

2

If you know the SQL & want to translate it into LINQ or if you know LINQ & want to translate it to SQL

Best tool is LinqPad

answered Oct 20 '17 at 19:09

Rohit Kumar

1,777
2
13
26

score 1 · Accepted Answer · answered Oct 20 '17 at 18:58

I don't understand what are you trying to do exactly, but your sql query can be written as following:

  SELECT DISTINCT bt.BookID
  FROM BooksTable AS bt
  JOIN PapersTable AS pt ON bt.BookID = pt.BookID
  JOIN PapersPropertiesTable AS ppt ON pt.PaperID = ppt.PaperID;

No need for the inner query.
No need for group by.
Just use DISTINCT.

You mihgt need to use INSERT INTO SELECT to insert the result of this query into another table:

  INSERT INTO Table2(BookID,PropID)
  SELECT DISTINCT bt.BookID, ppt.PropID
  FROM BooksTable AS bt
  JOIN PapersTable AS pt ON bt.BookID = pt.BookID
  JOIN PapersPropertiesTable AS ppt ON pt.PaperID = ppt.PaperID

score 1 · Answer 3 · answered Oct 20 '17 at 19:17

Reading your question and code and inferring a little bit from the code, my understanding is that you want to retrieve a list of BookIds with associated Paper Properties and then for each one execute a stored procedure. One way to accomplish this with a SQL-only approach rather than having C# orchestrate the calls would be to use a cursor in place of the result of the LINQ query. Here's an example:

DECLARE migrate_cursor CURSOR
FOR
SELECT DISTINCT bt.BookID, ppt.PropID
FROM BooksTable AS bt
  INNER JOIN PapersTable AS pt
    ON bt.BookID = pt.BookID
  INNER JOIN PapersPropertiesTable AS ppt
    ON pt.PaperID = ppt.PaperID;

OPEN migrate_cursor;

FETCH NEXT FROM migrate_cursor  
INTO @bookId, @propId;  

WHILE @@FETCH_STATUS = 0  
BEGIN  
   EXECUTE my_insert_props_procedure @bookId, @propId

   FETCH NEXT FROM migrate_cursor  
   INTO @bookId, @propId;  
END;

CLOSE migrate_cursor;  
DEALLOCATE migrate_cursor;

Now if this was a large resultset, you would want to take into consideration things like making the cursor forward-only and read-only and avoiding locks, but this gets the general idea across without overly complicating the example. You can get more detailed information from the docs at https://learn.microsoft.com/en-us/sql/t-sql/language-elements/cursors-transact-sql

Translating Linq Code Block into SQL

3 Answers3