Using Group By clause with Entity Framework in C# .Net

Last Reply on Sep 17, 2014 03:30 AM By mosin

Posted on Sep 17, 2014 02:36 AM

i have a scenario where i have to display the state name and number of cities in that state in autocompletet text how to do that

 

[HttpPost]
public JsonResult find(string Text)
{
    var suggestedUsers = (from x in lw.RWW_CountriesMaster
                            where x.Country.StartsWith(Text)
                            select new
                            {
                                country = x.Country,
                                capital = x.Capital
                            }).Distinct();
    var count = (from x in lw.RWW_CountriesMaster
                    where x.Country.StartsWith(Text)
                    select new
                    {
                        country = x.Country,
                        capital = x.Capital
                    }).Count();

    var result = suggestedUsers.Take(5).ToList();
    //return result;
    return Json(result, JsonRequestBehavior.AllowGet);
}

 

Posted on Sep 17, 2014 03:30 AM

I have made sample for count of cities in country.

1. Create System App_Code folder

2. Add .dbml file in App_Code folder

3. Drag and drop table from your database using server explorer

4. Add Connection string in web.config

5. Add new page with datacontext to refer your table

6. Follow the steps give below

Please refer following sample:

HTML

<div>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
        <Columns>
            <asp:BoundField HeaderText="Country" DataField="Country" />
            <asp:BoundField HeaderText="Count of cities" DataField="Count_of_cities" />
        </Columns>
    </asp:GridView>
</div>

Namespace:

using System.Linq;
using LINQ;

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindGrid();
    }
}

protected void BindGrid()
{
    using (DataClassesDataContext dc = new DataClassesDataContext())
    {
        var res = from c in dc.Customers
                    group c.City by c.Country into g              
                    select new 
                    { 
                        Country = g.Key, 
                        Count_of_cities = g.ToList().Count() 
                    };                      

        GridView1.DataSource = res.ToList();
        GridView1.DataBind();
    }
}

Screenshot

Structure of solution explorer

Customer Table

Output