Click here to Skip to main content
15,894,180 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
my table structure is like this

1)list_table
C#
id      int
list_name   string

2)item_table
C#
id      int
list_name   string
item_name   string
price       double
qty     int
amt     double

i want to disply it like
C#
list_name ( Count)

i want item count group by list name and also want to include those list_name whose
item is not there in item_table.(i.e. list_name (0) )

till i have done
C#
public class MyListItem
    	{
        public string category { get; set; }
        public double total { get; set; }
    	}

	public List<mylistitem> jinal;

	public void listcountwise1()
        {
	var qur = from lst in db.List1
                      join it in db.Item1 on lst.List_name equals it.list_name
                      group lst by new { lst.List_name } into g
                      from c in g.DefaultIfEmpty()
                      select new MyListItem() { category = g.Key.List_name, total  = (c == null ? 0 : g.Count()) };
            jinal = qur.ToList();
	}

the result is not true.
there is one list repeting no of items it have.
and the list which has no items is not displying it shows null insted of name.
Posted
Updated 5-Jun-12 1:08am
v2

1 solution

SQL
var q = from p in db.List1
                    join c in db.Item1 on p.List_name equals c.list_name into j1
                    from j2 in j1.DefaultIfEmpty()
                    group j2 by p.List_name into g
                    select new MyListItem() { category = g.Key, total = g.Count(t => t != null) };
it works...
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900