locked
How to show customer data and order count using LINQ RRS feed

  • Question

  • User1183902823 posted

    my table structures are

    Customer
    ----------
    CustomerID
    FirstName
    LastName
    PhoneNo
    Email
    
    Order
    --------
    CustomerID
    OrderID
    OrderStatus
    OrderDate
    TotalPrice
    
    OrderDetails
    ------------
    ID
    OrderID
    ProductID
    Qty
    

    so now i have to show CustomerID,FirstName,LastName,PhoneNo,Email and OrderCount

    please give me a sample EF linq query

    thanks

    Thursday, January 18, 2018 10:50 AM

Answers

  • User1183902823 posted

    i got one close to my requirement

    var data = db.Customers
        .Where(c => c.CustomerID == 101)
        .Select(c => new 
        {
            c.CustomerID,
            c.FirstName,
            c.LastName,
            c.PhoneNo,
            c.Email,
            OrderCount = db.Orders.Count(o => o.CustomerID == c.CustomerID)
        })
        .ToList();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 19, 2018 11:20 AM
  • User-832373396 posted

    <g class="gr_ gr_11 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="11" data-gr-id="11">Hi</g> <g class="gr_ gr_10 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="10" data-gr-id="10">tridip</g>,

    yeah, that is a way, and Sir, if we are using the EF, maybe you could view this way <g class="gr_ gr_12 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="12" data-gr-id="12">:) ,</g> it could save your code 

    var data = db.Customers.Include(a=>a.Orders)
        .Where(c => c.CustomerID == 101)
        .Select(c => new 
        {
            c.CustomerID,
            c.FirstName,
            c.LastName,
            c.PhoneNo,
            c.Email,
            OrderCount = c.Orders.Count()
        })
        .ToList();

    Way 2:

    Customer data = db.Customers.Include(a=>a.Orders)
        .Where(c => c.CustomerID == 101)   
        .FirstOrDefault();
    return View(data);

    at <g class="gr_ gr_13 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="13" data-gr-id="13">page</g>:

    @Model. FirstName

    @Model.Orders.Count()  //we could get the value directly

    The reason, in EF, it will get the Orders collection according to the CustomerID  automatically. it seems that customerID  of db.Customers.Include(a=>a.Orders) .Where(c => c.CustomerID == 101) .FirstOrDefault().Orders collection ,all are 101;

    in EF;

    public class Order
    {
        public int OrderID{ get; set; }
        ....
        public int CustomerID{get;set;}
        public virtual Customer Customer { get; set; }
    // virtual is required for using db.Customers.Include(a=>a.Orders),if not, the orders is null } public class Customer { public int CustomerID{ get; set; } public string FirstName{ get; set; } ... public virtual ICollection<Order> Orders{ get; set; } }

    http://www.entityframeworktutorial.net/code-first/configure-one-to-many-relationship-in-code-first.aspx 

    Welcome to back, if any question :)

    Bests,

    Jolie

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 21, 2018 8:12 AM
  • User-832373396 posted

    so how does it <g class="gr_ gr_6 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="6" data-gr-id="6">work ?</g>

    So in your code, it always needs to query the full Order table every time;

    OrderCount = db.Orders.Count(o => o.CustomerID == c.CustomerID)

    so in my <g class="gr_ gr_15 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="15" data-gr-id="15">case</g> it will <g class="gr_ gr_14 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace gr-progress" id="14" data-gr-id="14">be lazyload</g> the <g class="gr_ gr_16 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="16" data-gr-id="16">order ?</g>

    Yes. Maybe you have known that default, in ef, we don't need .include , it also will bind the sub-collection for us;

    • ep.
    foreach( var i in db.Customers.ToList()){
      Debug.WriteLine( i.Orders.FirstOrDefault().OrderID); // it could get the sub Orders collection successfully
    }

    Reason: Lazy loading is enabled by default. 

     public NorthwindContext() : base("Northwind")
            {
                this.Configuration.LazyLoadingEnabled = false;//and default , this.Configuration.LazyLoadingEnabled is true;

    Guide:

    http://www.entityframeworktutorial.net/lazyloading-in-entity-framework.aspx 

    Bests,

    Jolie

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 24, 2018 9:37 AM

All replies

  • User1693814044 posted

    please give me a sample EF linq query

    are you asking everybody to write your code for you (??!!) or are you having some trouble with code you've already written?

    Thursday, January 18, 2018 12:21 PM
  • User1183902823 posted

    see this is my sql

    SELECT CustomerID,FirstName,LastName,PhoneNo,Email and count(*) as OrderCount
    FROM Customers 
    INNER JOIN Orders ON Customers .CustomerID = Orders .CustomerID
    GROUP BY CustomerID,FirstName,LastName,PhoneNo,Email
    WHERE CustomerID=101

    and i need to write equivalent linq query. i tried this way taking help from internet.

    var data = (from c in db.customers 
                  join o in db.orders 
                  on c.CustomerID = o.CustomerID into subs
                  from sub in subs.DefaultIfEmpty()
                  group sub by new { c.CustomerID, c.FirstName,c.LastName,c.PhoneNo,c.Email  } into gr
                  select new {
                      gr.Key.CustomerID,
                      gr.Key.FirstName,
                      gr.Key.LastName,
                      gr.Key.PhoneNo,
                      gr.Key.Email,
                      OrderCount = gr.Count(x => x != null)
                  }).ToList();

    so now i am not before dev pc. so tell me does my above linq query will give me desired data ?

    thanks

    Thursday, January 18, 2018 12:48 PM
  • User1183902823 posted

    i got one close to my requirement

    var data = db.Customers
        .Where(c => c.CustomerID == 101)
        .Select(c => new 
        {
            c.CustomerID,
            c.FirstName,
            c.LastName,
            c.PhoneNo,
            c.Email,
            OrderCount = db.Orders.Count(o => o.CustomerID == c.CustomerID)
        })
        .ToList();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 19, 2018 11:20 AM
  • User-832373396 posted

    <g class="gr_ gr_11 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="11" data-gr-id="11">Hi</g> <g class="gr_ gr_10 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="10" data-gr-id="10">tridip</g>,

    yeah, that is a way, and Sir, if we are using the EF, maybe you could view this way <g class="gr_ gr_12 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="12" data-gr-id="12">:) ,</g> it could save your code 

    var data = db.Customers.Include(a=>a.Orders)
        .Where(c => c.CustomerID == 101)
        .Select(c => new 
        {
            c.CustomerID,
            c.FirstName,
            c.LastName,
            c.PhoneNo,
            c.Email,
            OrderCount = c.Orders.Count()
        })
        .ToList();

    Way 2:

    Customer data = db.Customers.Include(a=>a.Orders)
        .Where(c => c.CustomerID == 101)   
        .FirstOrDefault();
    return View(data);

    at <g class="gr_ gr_13 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="13" data-gr-id="13">page</g>:

    @Model. FirstName

    @Model.Orders.Count()  //we could get the value directly

    The reason, in EF, it will get the Orders collection according to the CustomerID  automatically. it seems that customerID  of db.Customers.Include(a=>a.Orders) .Where(c => c.CustomerID == 101) .FirstOrDefault().Orders collection ,all are 101;

    in EF;

    public class Order
    {
        public int OrderID{ get; set; }
        ....
        public int CustomerID{get;set;}
        public virtual Customer Customer { get; set; }
    // virtual is required for using db.Customers.Include(a=>a.Orders),if not, the orders is null } public class Customer { public int CustomerID{ get; set; } public string FirstName{ get; set; } ... public virtual ICollection<Order> Orders{ get; set; } }

    http://www.entityframeworktutorial.net/code-first/configure-one-to-many-relationship-in-code-first.aspx 

    Welcome to back, if any question :)

    Bests,

    Jolie

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 21, 2018 8:12 AM
  • User1183902823 posted

    @Joli see my code where i have not used include......so how does it work ?

    var data = db.Customers
        .Where(c => c.CustomerID == 101)
        .Select(c => new 
        {
            c.CustomerID,
            c.FirstName,
            c.LastName,
            c.PhoneNo,
            c.Email,
            OrderCount = db.Orders.Count(o => o.CustomerID == c.CustomerID)
        })
        .ToList();

    so in my case it will be lazyload the order ?

    Wednesday, January 24, 2018 8:02 AM
  • User-832373396 posted

    so how does it <g class="gr_ gr_6 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="6" data-gr-id="6">work ?</g>

    So in your code, it always needs to query the full Order table every time;

    OrderCount = db.Orders.Count(o => o.CustomerID == c.CustomerID)

    so in my <g class="gr_ gr_15 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="15" data-gr-id="15">case</g> it will <g class="gr_ gr_14 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace gr-progress" id="14" data-gr-id="14">be lazyload</g> the <g class="gr_ gr_16 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="16" data-gr-id="16">order ?</g>

    Yes. Maybe you have known that default, in ef, we don't need .include , it also will bind the sub-collection for us;

    • ep.
    foreach( var i in db.Customers.ToList()){
      Debug.WriteLine( i.Orders.FirstOrDefault().OrderID); // it could get the sub Orders collection successfully
    }

    Reason: Lazy loading is enabled by default. 

     public NorthwindContext() : base("Northwind")
            {
                this.Configuration.LazyLoadingEnabled = false;//and default , this.Configuration.LazyLoadingEnabled is true;

    Guide:

    http://www.entityframeworktutorial.net/lazyloading-in-entity-framework.aspx 

    Bests,

    Jolie

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 24, 2018 9:37 AM
  • User1183902823 posted

    @Joli you said --

    So in your code, it always needs to query the full Order table every time;

    OrderCount = db.Orders.Count(o => o.CustomerID == c.CustomerID)

    which not clear....what do you mean by query the full Order table every time

    Wednesday, January 24, 2018 11:37 AM
  • User-832373396 posted

    <g class="gr_ gr_28 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="28" data-gr-id="28">Hi</g> <g class="gr_ gr_17 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="17" data-gr-id="17">tridip</g>,

    my code where <g class="gr_ gr_6 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="6" data-gr-id="6">i</g> have not used include......so how does it <g class="gr_ gr_7 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="7" data-gr-id="7">work ?</g>

    var data = db.Customers
        .Where(c => c.CustomerID == 101)
        .Select(c => new 
        {
            c.CustomerID,
            c.FirstName,
            c.LastName,
            c.PhoneNo,
            c.Email,
            OrderCount = db.Orders.Count(o => o.CustomerID == c.CustomerID)
        })
        .ToList();

    Sir, we could understand as:

    var data = db.Customers
        .Where(c => c.CustomerID == 101).ToList();
    List<Object> result=new List<Object>();
    foreach(var c in data){
    var avalue= db.Orders.Count(o => o.CustomerID == c.CustomerID);// so, here, it need to query the Order table every time;
    var newObject= new { c.CustomerID, c.FirstName, c.LastName, c.PhoneNo, c.Email, OrderCount = avalue });
    result.append(newObject);
    }

    Bests,

    Jolie

    Thursday, January 25, 2018 10:40 AM