sql query change into LINQ

7 posts, 2 answers
  1. kd
    kd avatar
    23 posts
    Member since:
    Dec 2011

    Posted 08 Feb 2012 Link to this post

    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')
  2. Answer
    Ralph Waldenmaier
    Admin
    Ralph Waldenmaier avatar
    202 posts

    Posted 10 Feb 2012 Link to this post

    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
    Sharpen your .NET Ninja skills! Attend Q1 webinar week and get a chance to win a license! Book your seat now >>
  3. DevCraft banner
  4. kd
    kd avatar
    23 posts
    Member since:
    Dec 2011

    Posted 14 Feb 2012 Link to this post

    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;
            }

  5. Ralph Waldenmaier
    Admin
    Ralph Waldenmaier avatar
    202 posts

    Posted 14 Feb 2012 Link to this post

    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
    Sharpen your .NET Ninja skills! Attend Q1 webinar week and get a chance to win a license! Book your seat now >>
  6. kd
    kd avatar
    23 posts
    Member since:
    Dec 2011

    Posted 15 Feb 2012 Link to this post

    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,


     
  7. Answer
    Ralph Waldenmaier
    Admin
    Ralph Waldenmaier avatar
    202 posts

    Posted 17 Feb 2012 Link to this post

    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
    Sharpen your .NET Ninja skills! Attend Q1 webinar week and get a chance to win a license! Book your seat now >>
  8. kd
    kd avatar
    23 posts
    Member since:
    Dec 2011

    Posted 28 Feb 2012 Link to this post

    Hi Ralph ,
    Thanks for reply
Back to Top
DevCraft banner