SQL query into LINQ in asp.net mvc

Question

Please anyone can help me to write this sql query into Linq. i have tried..

this is my sql query

select  o.OrderID,o.Nature,o.Date,od.TotalPrice,os.OrderStatus,lo.FirstName,lo.EmailAddress,lo.PhoneNumber
from [dbo].[Order] o
    inner join [dbo].[tbl_OrderDetails] od on  od.OrderID = o.OrderID
    inner join [dbo].[tbl_OrderHistory] oh on oh.OrderID = o.OrderID
    inner join [dbo].[tbl_Login] lo on o.UserID = lo.UserID
    inner join dbo.tbl_OrderStatus os on oh.OrderStatusID= os.OrderStatusID
group by o.OrderID,o.Nature,od.TotalPrice,o.Date,os.OrderStatus,lo.FirstName,lo.EmailAddress,lo.PhoneNumber

and this is my try

public override orderDetailModel orderDetails(int id)
{
    var results = from o in obj.Orders
        join od in obj.tbl_OrderDetails on o.OrderID equals od.OrderID
        join oh in obj.tbl_OrderHistory on o.OrderID equals oh.OrderID
        join l in obj.tbl_Login on o.UserID equals l.UserID
        join os in obj.tbl_OrderStatus on oh.OrderStatusID equals os.OrderStatusID
        where (od.OrderID == id)
        group o by new { o.Nature, o.OrderID } into 
        select new orderDetailModel
        {
            OrderID = o.OrderID,
            OrderStatus = os.OrderStatus,
            Date = o.Date,
            DeliveryNature = o.Nature,
            EmailAddress = l.EmailAddress,
            FirstName = l.FirstName,
            PhoneNumber = l.PhoneNumber,
            TotalPrice = od.TotalPrice
        };
    //group o by new {o.OrderID};
    orderDetailModel data = (orderDetailModel)results.FirstOrDefault();
    return data;
}

but this is wrong query its not working fine please help me

"but this is wrong query its not working fine" What does this mean? Do you get errors? Is the query returning whrong elements? If so what did you expect instead? — MakePeaceGreatAgain
– MakePeaceGreatAgain, Commented Nov 23, 2015 at 16:13
i mean when i write group by code is show errors on select and o.orderID,so how can i put group by in this code? — Sam khan
– Sam khan, Commented Nov 23, 2015 at 16:15
Actually I cennot see any meaning in grouping by a TotalPrice?. However your grouping within LINQ seems quite different as this within SQL. Why do you write this by new {...} stuff? — MakePeaceGreatAgain
– MakePeaceGreatAgain, Commented Nov 23, 2015 at 16:21
Rather than use joins in Linq you really should try to use the Navigation Properties coding.abel.nu/2012/06/dont-use-linqs-join-navigate — juharr
– juharr, Commented Nov 23, 2015 at 16:22

Rahul Singh · Accepted Answer · 2015-11-23 16:16:43Z

1

You need to correct the group by clause, like you have in the SQL query like this:-

group new { o, l } by new {  o.OrderID,o.Nature,od.TotalPrice,o.Date,os.OrderStatus,
                             l.FirstName, l.EmailAddress,l.PhoneNumber } into g
                      select new orderDetailModel
                      {
                          OrderID = g.Key.OrderID,
                          OrderStatus = g.Key.OrderStatus,
                          Date = g.Key.Date,
                          ..and so on
                      };

Since you need the grouping on two tables Order & tbl_Login you will have to first project them as anonymous type group new { o, l } then specify all the groupings and finally while projecting use Key to get the respective items.

answered Nov 23, 2015 at 16:16

Rahul Singh

21.8k6 gold badges43 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam khan Over a year ago

Ohh Thank you Rahul its working for me i really need this exactly

alessalessio · Accepted Answer · 2015-11-23 17:07:33Z

I guess that actually, also the SQL query is not correct. I would simply use a SELECT DISTINCT ... instead of Grouping all the columns.

Anyway, first thing to do:

Check if databases is designed correctly. As far as i can see, if you're joining the table with their Ids, i don't understand why you need to group all the data. If you have duplicates, maybe the error is in the Database design.

If you can't change your Database, or you are happy with it, then use the following LINQ approach:

var distinctKeys = allOrderDetails.Select(o => new { o.OrderID, o.Nature, o.TotalPrice, o.Date,o.OrderStatus,o.FirstName, o.EmailAddress,o.PhoneNumber }).Distinct();

var joined = from e in allOrderDetails
             join d in distinctKeys
             on new { o.OrderID, o.Nature,o.TotalPrice, o.Date,o.OrderStatus, o.FirstName, o.EmailAddress, o.PhoneNumber }   equals d select e;

joined.ToList(); // gives you the distinct/grouped list

Collectives™ on Stack Overflow

SQL query into LINQ in asp.net mvc

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related