CODEWITHSUNDEEP
c#sql-serverlinq
Sam khan
Sam khan

Reputation: 220

SQL query into LINQ in asp.net mvc

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

Upvotes: 1

Views: 171

Answers (2)

alessalessio
alessalessio

Reputation: 1234

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:

  1. 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.

  2. 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

Upvotes: 0

Rahul Singh
Rahul Singh

Reputation: 21825

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.

Upvotes: 1

Related Questions