Translating SQL Queries to LINQ

Question

How do I translate SQL Queries to LINQ? Can you please help me translate the following SQL query to LINQ?

SELECT  Top 1 @SalesRepID = Part.KUNN2  
FROM x Part  with(nolock)  
JOIN y UserCustomer with(nolock)  
ON Part.KUNN2 = UserCustomer.Customer  
LEFT JOIN z[User] with(nolock)  
ON UserCustomer.UserId = [User].UserId   
AND [User].UserDisabled != 'Y'
WHERE (Part.PARVWxx = 'ZP' or Part.PARVWxx = 'ZR' )   
AND Part.KUNNRxx =  @Customer  
ORDER BY Part.KUNNRxx, Part.PARVWxx DESC

What ORM are you using? Entity Framework, NHibernate, Linq-to-SQL, etc — JNYRanger, Mar 03 '15 at 00:37

score 0 · Answer 1 · answered Mar 03 '15 at 00:43

You can play with the following here: https://dotnetfiddle.net/wcqLUx It might not be exactly what you need; it will get you started though. I needed to make some assumptions; you can clear those up in the fiddle. The following is the best that I could do in 20 minutes or so.

void Main()
{
    IEnumerable<User> Users = 
        new List<User> { new User { UserId = 0, UserDisabled = "N" } };
    IEnumerable<Part> Parts = 
        new List<Part> { new Part { SalesRepId = 0, PARVWxx = "ZP", KUNNRxx = "@Customer" } };
    IEnumerable<UserCustomer> UserCustomers = 
        new List<UserCustomer> { new UserCustomer { UserId = 0, Customer = 0 } };

    var query = 
        from p in Parts
        join y in UserCustomers 
        on p.SalesRepId equals y.Customer
        select new { p, y } into inner
            join z in Users 
            on inner.y.UserId equals z.UserId
            where ! z.UserDisabled.Equals("Y") && 
            inner.p.PARVWxx.Equals("ZP") && 
            inner.p.KUNNRxx.Equals("@Customer")
            select inner.p.SalesRepId;

    foreach(var result in query)
    {
        Console.WriteLine(result);
    }
}

// Define other methods and classes here
public class Part 
{
    public int SalesRepId { get; set; }
    public string PARVWxx { get; set; }
    public string KUNNRxx { get; set; }
}

public class User
{
    public int UserId { get; set; }
    public string UserDisabled { get; set; }
}

public class UserCustomer
{
    public int UserId { get; set; }
    public int Customer { get; set; }
}

Translating SQL Queries to LINQ

1 Answers1