How to show customer data and order count using LINQ

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

Answer 1

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?

Answer 2

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

Answer 3

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();

Answer 4

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

Answer 5

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 ?

Answer 6

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

Answer 7

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

Answer 8

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