sql query change into LINQ

[kd]

Hi all,
i am facing problem doing group on table how can i write this sql query into linq c#

select
UserProfile.ID,
UserProfile.FirstName+' '+UserProfile.LastName UserName,
IsNull(CurrentBid.[Current],0)CurrentBid,
IsNull(WinnerBid.Winner,0)WinnerBid,
IsNull(LoseBid.Lose,0)LoseBid,
IsNull(CancelledBid.Cancel,0)CancelledBid
from UserProfile
left join
(
select count(BidTransactions.ID) [Current],UserID
from BidTransactions where
IsCloseBid= null or IsCloseBid = 0
group by UserID)CurrentBid on CurrentBid.UserID = UserProfile.ID
left Join (
select count(BidTransactions.ID) [Winner] ,UserID
from BidTransactions where
IsCloseBid= 1 AND IsWinnerBid = 1
group by UserID)WinnerBid on WinnerBid.UserID = CurrentBid.UserID
left Join(
select count(BidTransactions.ID) [Lose],UserID
from BidTransactions where
IsCloseBid= 1 AND IsWinnerBid = 0
group by UserID)LoseBid on LoseBid.UserID = CurrentBid.UserID
left join(
select count(BidTransactions.ID) [Cancel],UserID
from BidTransactions where
IsCancelled = 1
group by UserID) CancelledBid on CancelledBid.UserID = CurrentBid.UserID
where UserProfile.RoleName in ('Sellers','Bidders')

[Ralph Waldenmaier]

Hello kd,

I have prepared an example, using the Northwind data model. The LINQ query demonstrates, how you could build your LINQ query to get the desired result. I think it can be easily adapted to your specific case.

using (EntitiesModel ctx = new EntitiesModel())
{
    var Orders = ctx.Orders;
    var OrderDetails = ctx.OrderDetails;
    var qry =
    from o in Orders
    join od in
        (from od in OrderDetails
            where od.UnitPrice > 2
            group od by od.Order into g
            select new { Ord = g.Key, Vls = g.Count() }
            ) on o equals od.Ord into ps
    from od in ps.DefaultIfEmpty()
    join od2 in
        (from od in OrderDetails
            where od.UnitPrice > 4
            group od by od.Order into g
            select new { Ord = g.Key, Vls = g.Count() }
        ) on o equals od2.Ord into ps2
    from od2 in ps2.DefaultIfEmpty()
    select new
    {
        OrderId = o.OrderID,
        SomeVal = od2 == null ? 0 : od2.Vls,
        SomeOtherVal = od == null ? 0 : od.Vls
    };

    var rslt = qry.ToList();
}

Unfortunately Telerik OpenAccess ORM does not exactly create the query that you have provided.
In order to execute your provided query directly, you can use our ADO API.
See this link to get more details about our ADO API. 

Do get back in case you need further assistance.

Regards,
Ralph
the Telerik team

[kd]

thanks for reply i did this in this way but it performance not good

public static IQueryable GetAllBidderTransaction()
        {
            DAL.DataContext dC = new DAL.DataContext();
            DAL.HunchClub_db hcEntities = dC.GetHCDataEntities();

            var BTP = from uP in hcEntities.UserProfiles
                      where uP.RoleName.Equals(HunchClub.Common.Constants.RoleNames.Sellers) || uP.RoleName.Equals(HunchClub.Common.Constants.RoleNames.Bidders)
                      join c in
                          (from t in hcEntities.BidTransactions
                           where t.IsCloseBid == null || t.IsCloseBid == false && t.IsCancelled == null || t.IsCancelled == false
                           group t by new { t.UserID } into x
                           where x.Count() > 0
                           select new { UserID = x.Key.UserID, Current = x.Count() }
                           ) on uP.ID equals c.UserID into current
                      from c in current.DefaultIfEmpty()
                      join w in
                          (from t in hcEntities.BidTransactions
                           where t.IsCloseBid == true && t.IsCloseBid == true
                           group t by new { t.UserID } into x
                           where x.Count() > 0
                           select new { UserID = x.Key.UserID, Won = x.Count() }
                          ) on uP.ID equals w.UserID into won
                      from w in won.DefaultIfEmpty()
                      join l in
                          (
                              from t in hcEntities.BidTransactions
                              where t.IsCloseBid == true && t.IsWinnerBid == false
                              group t by new { t.UserID } into x
                              where x.Count() > 0
                              select new { UserID = x.Key.UserID, Lost = x.Count() }
                            ) on uP.ID equals l.UserID into los
                      from l in los.DefaultIfEmpty()
                      join cn in
                          (
                              from t in hcEntities.BidTransactions
                              where t.IsCancelled == true
                              group t by new { t.UserID } into x
                              where x.Count() > 0
                              select new { UserID = x.Key.UserID, Cancel = x.Count() }
                            ) on uP.ID equals cn.UserID into cancel
                      from cn in cancel.DefaultIfEmpty()
                      select new
                      {
                          UserID = uP.ID,
                          UserName = uP.FirstName + " " + uP.LastName,
                          Current = c.Current == null ? 0 : c.Current,
                          Won = w.Won == null ? 0 : w.Won,
                          Lost = l.Lost == null ? 0 : l.Lost,
                          Cancel = cn.Cancel == null ? 0 : cn.Cancel
                      };

            return BTP;
        }

[Ralph Waldenmaier]

Hi kd,

The query you try to execute is fairly complex and we are not able to push the complete query to the server and have everything calculated by the server. You can see the exact behavior if you enable logging of your application an see what queries are executed.
In order to execute this complex scenario, I'd  like to ask you, if you could try our ADO API. There you will have the chance to fire exactly your desired query. Additionally, we provide a mechanism to translate the result into the objects of your choice. Therefore we have a Translate method on the Context. See this link for details on our ADO API.

I hope this information is helpful for you. Feel free to ask if you have any other question.

Greetings,
Ralph
the Telerik team

[kd]

Hi Ralph,

Thanks for rply.
Actually i have not enough knowledge about entity frame work and linq so i am still confused in following senerio.
for example a db table has follwoing fields.
ID,UserID,IsApproved,IsCancelled,IsWon,IsActive
first tw0 columns type is guid and all others are bool so i want to show all approved, cancelled , won and Current transactions in a grid that each user has how much current, cancelled, Won and Lost bids
eg:-
Users - BidDetail - Current - Cancelled - Won - Lost
abc     |  nokia      |   30         |   2           |     10 |   6   

i have did this with my previous query which you have already seen, 
so how do i know that is that query performance is acceptable for my site are doing slow my site , and what is the best technique to did if group by and losts of joins are needed for a query,


 

[Ralph Waldenmaier]

Hello kd,

As I said, I think the best way to do such a query at the moment is to use our ADO API. Your implementation could look like the following scenario.

private static void Sample2()
       {
           // 1. Create a new instance of the OpenAccessContext.
           using (EntitiesModel dbContext = new EntitiesModel())
           {
               // 2. Retrieve the OAConnection instance.
               OAConnection oaConnection = dbContext.Connection;

               // 3. Create a string containing the sql query.
               string sqlQueryString = "YOUR COMPLEX QUERY HERE";

               // 4. Create a new instance of the OACommand class.
               using (OACommand oaCommand = oaConnection.CreateCommand())
               {
                   // 5. Set the CommandText property.
                   oaCommand.CommandText = sqlQueryString;

                   // 6. Execute the command.
                   using (DbDataReader reader = oaCommand.ExecuteReader())
                   {
                       // 7. Materialize the reader.
                       IEnumerable<YOURMAPPEDOBJECT> myResult = dbContext.Translate<YOURMAPPEDOBJECT>(reader);
                   }
               }
           }
       }

To your question regarding the foresight of the performance outcome. In general, there is no rule which says: "If you do something in that way, you will always have performance problems".
This is generally always dependent on the specific situation. 

After looking again at you SQL query, wouldn't it be possible rewrite your query that it might look like this example?

SELECT [t4].[OrderID], SUM(CASE [t7].[CategoryName] WHEN 'Beverages' THEN 1 ELSE 0 END)
 FROM [Orders] AS [t4]
 LEFT OUTER JOIN [Order Details] AS [t5] ON [t4].[OrderID] = [t5].[OrderID]
 LEFT OUTER JOIN [Products] AS [t6] ON [t6].[ProductID] = [t5].[ProductID]
 LEFT OUTER JOIN [Categories] AS [t7] ON [t7].[CategoryID] = [t6].[CategoryID]
 GROUP BY [t4].[OrderID]

Using this pattern might lead to a faster query execution on the server side. Combined with the ADO API way, you should have your results as fast as possible.

Feel free to ask if you have any other question.
Greetings,
Ralph
the Telerik team

[kd]

Hi Ralph ,
Thanks for reply