Convert SQL Query to LINQ-to-SQL

Question

I need help converting SQL query to LINQ to SQL

select top 5 customer_id, customer_name, product_id
from Customer 
Join Product on product_id = product_id
where (customer_active = 'TRUE')
order by checksum(newid())

How can I do that in LINQ to SQL. Thanks

It is a lil bit weird because => `product_id = product_id` will alwyas return true. Are you sure that your SQL works? Anyway can we have the entities class and your DbContext? — CodeNotFound, May 08 '17 at 16:38
yeah this is just a example data, SQL is working fine like you suggested. Thanks — aadi1295, May 08 '17 at 16:41
@CodeNotFound: Please check out below, This is how I did in my code (This is just an example). If you want me to mark this accepted answer, please add your and I will mark it. Thanks — aadi1295, May 08 '17 at 16:45
Glad you find the solution. You're welcome. Thanks. Just accept your own answer ;-) — CodeNotFound, May 08 '17 at 16:48

score 1 · Accepted Answer · edited May 23 '17 at 12:18

This was solved. Thanks to 'CodeNotFound' for this answer https://stackoverflow.com/a/43850748/1655774

db.Customer.Where(p => p.customer_active == true).Select(p => new CustomerViewModel
    {
         Customer_id= p.customer_id,
         Customer_name = p.customer_name,
         Product_id = p.Product.product_id
    }).OrderBy(c => SqlFunctions.Checksum(Guid.NewGuid())).Take(5).ToList();

score 0 · Answer 2 · answered May 10 '17 at 09:30

try this code

( from p in  Customer  
         join q in Product  on p.product_id  equals q.product_id
         join q in Product  on p.product_id  equals q.product_id
         where customer_active ==true  select new 
         {
            customer_id=p.customer_id,
            customer_name=p.customer_name,
            product_id=q.product_id
        }).OrderBy(c => SqlFunctions.Checksum(Guid.NewGuid())).Take(5).ToList();

score 0 · Answer 3 · answered May 16 '17 at 11:35

you should use this way to remove Boolean condition and reduce code

if you need to check bool condition in Ef

1.For True Condition db.Customer.Where(p => p.customer_active).select(m=>m).tolist(); 1.For False Condition db.Customer.Where(p => !p.customer_active).select(m=>m).tolist();

just for suggestion

Convert SQL Query to LINQ-to-SQL

3 Answers3

Linked