LinqPad - Convert SQL to Linq command

Question

I recently purchased LINQPad in hopes that it would allow me to convert SQL statements into LINQ statements.

Using LINQPad, I am able to attach a DB and run the SQL statement which returns the results I need.

But I can not find a 'command' to convert that SQL statement into LINQ.

Can you please let me know how to convert SQL to LINQ by using LINQPad OR another tool?

Also see this question: http://stackoverflow.com/questions/296972/sql-to-linq-tool — Charles Clayton, Oct 26 '15 at 23:09

Joe Albahari · Answer 1 · 2012-09-03T00:46:57.223

There is a tool called Linqer, but be careful: transliterating from SQL to LINQ can give you the worst of both worlds.

For instance, suppose you want all purchases of $1000 or greater paid for in cash or by customers who live in Washington. Here's the query in SQL:

SELECT p.*
FROM Purchase p
    LEFT OUTER JOIN 
        Customer c INNER JOIN Address a ON c.AddressID = a.ID
    ON p.CustomerID = c.ID  
WHERE
   (a.State = 'WA' || p.CustomerID IS NULL)
    AND p.ID in
    (
        SELECT PurchaseID FROM PurchaseItem
        GROUP BY PurchaseID HAVING SUM (SaleAmount) > 1000
    )

How would translate this to LINQ? The wrong way is to transliterate the query into LINQ, trying to reproduce the outer and inner joins, subquery and group clause. The right way is to map your original query (in English) directly into LINQ, leveraging LINQ's linear flow of data and association properties:

I want all purchases...

from p in db.Purchases

...of $1000 or greater...

where p.PurchaseItems.Sum (pi => pi.SaleAmount) > 1000

...paid for in cash...

where p.Customer == null

...or by customers who live in Washington

|| p.Customer.Address.State == "WA"

Here's the final query:

from p in db.Purchases
where p.PurchaseItems.Sum (pi => pi.SaleAmount) > 1000
where p.Customer == null || p.Customer.Address.State == "WA"
select p

More info here.

[how to convert MySQL to LINQ](https://stackoverflow.com/questions/51057323/unable-to-convert-mysql-query-to-linq)? — Moeez, Jun 27 '18 at 08:18

score 10 · Accepted Answer · answered Sep 02 '12 at 18:24

10

In general there are no tools to covert SQL to Linq as @andres-abel mention before, but sometimes you have to write Linq that will execute exactly as specified SQL (for example because of performance issues, backward compatability or some other reasons).

In this case I'll advice you to do reverse engineering by yourself:

configure logging of dump SQL statements generated by Linq to stdout using
manually rewrite Linq statement until you'll get what you need

answered Sep 02 '12 at 18:24

Akim

8,469
2
31
49

2

Linqpad does have a really nice translation of the Linq query to SQL (although it does so using the .Net Linq-to-SQL library, so your mileage may vary). I've found it does a great job of educating those just starting on Linq but familiar with SQL as well as verifying that what I wrote in Linq is producing results analogous to a SQL query. – Elsimer Jan 11 '13 at 13:00

score 8 · Answer 3 · answered Sep 02 '12 at 18:05

8

LinqPad contains no SQL->LINQ translator. LinqPad does actually not contain any LINQ->SQL translator either. It relies on the .Net Linq-to-Sql library or Entity framework for the translation.

I don't know of any other tool with that capability either. In simple cases it would be possible to make one, but for more complex scenarios it would be impossible as there is no LINQ expression that matches some SQL constructs.

answered Sep 02 '12 at 18:05

Anders Abel

67,989
17
150
217

You might mean vice versa. It contains `LINQ -> SQL` instead. – Artfaith Aug 01 '20 at 14:18

LinqPad - Convert SQL to Linq command

3 Answers3

Linked