how to write the count function in join

Posted by Srujana under ASP.NET on 5/26/2012 | Points: 10 | Views : 1273 | Status : [Member] | Replies : 8
Hi,
I have two tables product and order,
I need to display number of orders for each product.
For this I want to use count with join but its not working.
which is the correct way to display product name and number of orders using count function.
public Action select()
{
var product = (from u in db.Orders
join d in db.Products on u.ProductID equals d.ProductID
select new

{

d.Name,
count = u.Ord.........


}).ToList();
}




Responses

Posted by: Sakthi.Singaravel on: 5/26/2012 [Member] Silver | Points: 25

Up
0
Down
Try this,

public Action select() 


{
var product = (from u in db.Orders
join d in db.Products on u.ProductID equals d.ProductID
select new

{

d.Name,
count = u.Orders.Count()

}).ToList();
}


Regards,
Singaravel M

Srujana, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 5/26/2012 [Member] Silver | Points: 25

Up
0
Down
In this link have query same as u'r query...

See this...

http://forums.asp.net/t/1342535.aspx/1

Regards,
Singaravel M

Srujana, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sekar.C on: 5/26/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,
send your DB table structure for both tables

Regards
Sekar.c

Srujana, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Srujana on: 5/28/2012 [Member] Starter | Points: 25

Up
0
Down
public class Product : BaseModel
{
[Key]
public int ProductID { get; set; }

[Required]
//[DisplayName("Group")]
[LocalizedDisplayName("FIRST_NAME", NameResourceType = typeof(Strings))]
public int ProductGroupID { get; set; }

[Required]
[DisplayName("Product")]
public int ProductNameID { get; set; }

[DisplayName("Type")]
public int? ProductTypeID { get; set; }

[Required]
[StringLength(100)]
[DisplayName("Other Name")]
public string Name { get; set; }

[Required]
[StringLength(100)]
[DisplayName("Brand")]
public string BrandName { get; set; }

[Required]
[StringLength(100)]
[DisplayName("Quality")]
public string Quality { get; set; }



[Required]
[StringLength(100)]
[DisplayName("Size - Colli")]
public string ColliSize { get; set; }

[Required]
[StringLength(100)]
[DisplayName("Colli Dimensions")]
public string ColliDimensions { get; set; }

[ScriptIgnore]
public virtual ProductGroup ProductGroup { get; set; }

[ScriptIgnore]
public virtual ProductName ProductName { get; set; }

[ScriptIgnore]
public virtual ProductType ProductType { get; set; }

[ScriptIgnore]
public virtual ColliType ColliType { get; set; }

[ScriptIgnore]
public virtual Country Country { get; set; }

[ScriptIgnore]
public virtual Currency Currency { get; set; }

[ScriptIgnore]
public virtual ICollection<PreferredLocation> PreferredLocations { get; set; }

[ScriptIgnore]
public virtual ICollection<Order> Orders { get; set; }
}
}
public class ProductName : BaseModel
{
[Key]
public int ProductNameID { get; set; }

[DisplayName("Product")]
[StringLength(50)]
public string Name { get; set; }

[Required]
public int ProductGroupID { get; set; }

[ScriptIgnore]
public virtual ProductGroup ProductGroup { get; set; }

[ScriptIgnore]
public virtual ICollection<Product> Products { get; set; }

[ScriptIgnore]
public virtual ICollection<ProductType> ProductTypes { get; set; }
}

public class Order : BaseModel
{
[Key]
public int OrderID { get; set; }

[Required]
[DisplayName("Colli Count")]
public int ColliCount { get; set; }

[Required]
[DisplayName("Order Available?")]
public bool OrderAvailable { get; set; }

[Required]
[DisplayName("Order Shipped")]
public bool OrderShipped { get; set; }

[Required]
[DisplayName("Order Completed")]
public bool OrderCompleted { get; set; }

[DisplayName("Order Cancelled")]
public bool OrderCancelled { get; set; }

[Required]
[ForeignKey("Buyer")]
public int BuyerID { get; set; }

[Required]
[ForeignKey("Seller")]
public int SellerID { get; set; }

[Required]
public int ProductID { get; set; }

//public int OfferID { get; set; }

[Required]
public int PreferredLocationID { get; set; }

public float? Latitude { get; set; }

public float? Longitude { get; set; }

[ScriptIgnore]
public virtual User Buyer { get; set; }

[ScriptIgnore]
public virtual User Seller { get; set; }

[ScriptIgnore]
public virtual Product Product { get; set; }

public virtual PreferredLocation PreferredLocation { get; set; }

}
These are three model classes,
I need to retrieve no.of orders for each product.



Srujana, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sabarimahesh on: 5/31/2012 [Member] Bronze | Points: 25

Up
0
Down
http://stackoverflow.com/questions/6960501/asp-net-visitors-count-online-users-for-today-yesterday-in-net-4

Life is a Race
Thanks & Regards
By
Sabari Mahesh P M

Srujana, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sabarimahesh on: 5/31/2012 [Member] Bronze | Points: 25

Up
0
Down
http://stackoverflow.com/questions/9205702/html-select-items-count-in-asp-net

Life is a Race
Thanks & Regards
By
Sabari Mahesh P M

Srujana, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 5/31/2012 [Member] Silver | Points: 25

Up
0
Down
http://vbcity.com/forums/p/162270/694357.aspx

Regards,
Singaravel M

Srujana, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 5/31/2012 [Member] Silver | Points: 25

Up
0
Down
http://bytes.com/topic/net/answers/497381-use-count-function-xpath

Regards,
Singaravel M

Srujana, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response