entity framework group by lambda expression is not working?

Question

i am working on .net MVC a quite few days.and i wants to show a table where i send sum of some column and group by to the view. the problem i am facing that =>(action)

    public ActionResult Index()
    {
        TempData["UserDetails"] = db.UserDetails.Include(x => x.User).ToList();
        var financeofstudents = db.Financeofstudents.Include(f => f.Batche).Include(f => f.User).GroupBy(x => x.UserId).Select(x => new 
        {
            debit = x.Sum(item => item.debit),
            credit = x.Sum(item => item.credit),
            balance = x.Sum(item => item.balance)
        }).ToList();
        return View(financeofstudents);
    }

yah! may be its work fine but i don't know. because i am getting several error on client side.

when i am just sending a list its work file on client side.like =>

var financeofstudents = db.Financeofstudents.Include(f => f.Batche).Include(f => f.User).ToList();

but when i use this=>(because i need a group by) ==>

        var financeofstudents = db.Financeofstudents.Include(f => f.Batche).Include(f => f.User).GroupBy(x => x.UserId).Select(x => new 
        {
            debit = x.Sum(item => item.debit),
            credit = x.Sum(item => item.credit),
            balance = x.Sum(item => item.balance)
        }).ToList();

i am getting Error like =>

and my view =>

        @using TSMS_Atp_2_Final_Project.Models.Com.Tsms
        @model IEnumerable<Financeofstudent>

        @{
            ViewBag.Title = "Finance";
            IEnumerable<UserDetail> UserDetailsTable = TempData["UserDetails"] as IEnumerable<UserDetail>;    
        }

        <h2>Available Finance Detail Of Students...</h2>

        <p>
            <a href="@Url.Action("ASIB", "Batche")" class="btn btn-primary btn-sm">Assign New</a>
        </p>
        <div class="table-responsive">
            <table class="table table-striped table-inverse table-bordered">
                <tr>
                    <th>
                        @Html.DisplayNameFor(model => model.Batche.batch_code)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.Batche.name)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.User.UserId)
                    </th>
                    <th>
                        @Html.Label("Full Name")
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.debit)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.credit)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.balance)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.lastTrunsaction)
                    </th>
                    <th></th>
                </tr>

                @foreach (var item in Model)
                {
                    <tr>
                        <td>
                            @Html.ActionLink(item.Batche.batch_code, "Details", "Batche", new { id = item.Batche.batch_code }, null)
                        </td>
                        <td>
                            @Html.ActionLink(item.Batche.name, "Details", "Course", new { id = item.Batche.name }, null)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.User.UserId)
                        </td>
                        <td>
                            @{
                                var UserDetailsId = UserDetailsTable.Where(x => x.UserId == item.User.UserId).Select(x => x.id).FirstOrDefault();
                                var FullName = UserDetailsTable.Where(x => x.UserId == item.User.UserId).Select(x => x.fullname).FirstOrDefault();
                            }
                            @Html.ActionLink(FullName, "Details", "Students", new { id = UserDetailsId }, null)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.debit)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.credit)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.balance)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.lastTrunsaction)
                        </td>
                        <td>
                            <div style="width:80px;">
                                @Html.ActionLink("Edit", "Edit", new { id = item.id }) |
                                @Html.ActionLink("Details", "Details", new { id = item.id }) |
                                @Html.ActionLink("Delete", "Delete", new { id = item.id })
                            </div>
                        </td>
                    </tr>
                }
            </table>
        </div>

then i search on the net and found that i need to use =>

@model IEnumerable<System.Linq.IGrouping<object,Financeofstudent>>

instate of

@model IEnumerable<Financeofstudent>

this ...

i used and maybe the problem is solved!.but after that now i am getting error like=> i am tried to find the solution and found

The model item passed into the dictionary is 'System.Data.Entity.Infrastructure.DbQuery`, but requires 'System.Collections.Generic.IEnumerable

may be its the solution but am not understanding anything.

my model =>

    public class Financeofstudent
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int id { get; set; }
    /// <summary>
    /// /////////
    /// </summary>
    [Required(AllowEmptyStrings = false, ErrorMessage = "User Id Is Required!")]
    [MaxLength(12, ErrorMessage = "The Max length Of User ID is 12 character!")]
    [RegularExpression("[1-3]{2}-[0-9]{5}-[123]{1}|[1-3]{2}-[0-9]{7}-[123]{1}", ErrorMessage = "Invalid Id,It should [xx]-[xxxxx]-[x] or [xx]-[xxxxxxx]-[x]!")]
    [Display(Name = "User ID")]
    public string UserId { get; set; }
    /// <summary>
    /// ////////
    /// </summary>
    [Required(AllowEmptyStrings = false, ErrorMessage = "Batch Code Is Required!")]
    [MaxLength(700, ErrorMessage = "The Max Length For Batch Code Is 700 Character!")]
    [Display(Name = "Batch Code")]
    public string batch_code { get; set; }
    /// <summary>
    /// ////////
    /// </summary>
    [Required(ErrorMessage = "Debit Is Required!")]
    [RegularExpression("^[0-9]*$", ErrorMessage = "Invalid Number!")]
    [Display(Name = "Debit Amount")]
    public int debit { get; set; }
    /// <summary>
    /// ////////
    /// </summary>
    [Required(ErrorMessage = "Credit Is Required!")]
    [RegularExpression("[0-9]*", ErrorMessage = "Invalid Number!")]
    [Display(Name = "Credit Amount")]
    public int credit { get; set; }
    /// <summary>
    /// /////////
    /// </summary>
    [Required(ErrorMessage = "Balance Is Required!")]
    [RegularExpression("[0-9]*", ErrorMessage = "Invalid Number!")]
    [Display(Name = "Current Balance")]
    public int balance { get; set; }
    /// <summary>
    /// //////////
    /// </summary>
    [DataType(DataType.Date, ErrorMessage = "Invalid Date!")]
    [Display(Name = "Last Trunsaction Date")]
    public DateTime lastTrunsaction { get; set; }

    [Display(Name = "Date Of Entry")]
    [DataType(DataType.DateTime, ErrorMessage = "Invalid Date!")]
    public DateTime entry_date { get; set; }

    ///relationship with anothere table

    [ForeignKey("UserId")]
    public User User { get; set; }
    /// <summary>
    /// ///////
    /// </summary>
    [ForeignKey("batch_code")]
    public Batche Batche { get; set; }
}

and i am expecting something like =>

and query =>

select UserId,sum(debit),sum(credit),sum(balance) from Financeofstudents group by UserId;

on client side like above table? please help me.how can i solve my problem.or i am doing something wrong.or there are any other approach to get my desire output.

Your creating a collection of anonymous objects and passing it to a view expecting a collection of `Financeofstudent` - they need to be the same types (refer [The model item passed into the dictionary is of type .. but this dictionary requires a model item of type](http://stackoverflow.com/questions/40373595/the-model-item-passed-into-the-dictionary-is-of-type-but-this-dictionary-requ). Create a view model containing the properties you want in the view and use `.Select(x => new yourViewModel { ....` and us that view model in the view — , May 20 '17 at 21:52

score 0 · Answer 1 · answered May 20 '17 at 21:56

0

Your group by returns just a list of 3 integers. That list is passend as they model to your view. You don't have access to any of the database object properties.

Basicly, the SQL executed only fetches a list of 3 Sums...

answered May 20 '17 at 21:56

Wim Reymen

190
2
4

score 0 · Answer 2 · answered May 20 '17 at 22:33

look at this code:

.Select(x => new 
    {
        debit = x.Sum(item => item.debit),
        credit = x.Sum(item => item.credit),
        balance = x.Sum(item => item.balance)
    }).ToList();

it return anonymous type, but your view used IEnumerable< Financeofstudent> model.

Try to do this:

Create class FinanceofstudentModel

class FinanceofstudentsModel
{
    public string UserId { get; set; }
    public int Debit { get; set; }
    public int Credit { get; set; }
    public int Balance { get; set; }
}

than change your select to something like this

var financeofstudents = db.Financeofstudents
                          .Include(f => f.Batche)
                          .Include(f => f.User)
                          .GroupBy(x => x.UserId)
                          .Select(x => new FinanceofstudentsModel()
                                      {
                                          UserId = x.Key,
                                          Debit = x.Sum(item => item.debit),
                                          Credit = x.Sum(item => item.credit),
                                          Balance = x.Sum(item => item.balance)
                                      });

use in your view this model

@model IEnumerable<FinanceofstudentModel>

Your view may looks like this:

@model IEnumerable<FinanceofstudentModel>
@foreach (var fm in Model)
{
    <p>User: @fm.UserId Debit: @fm.Debit Credit: @fm.Credit Balance: @fm.Balance</p>
    <hr/>
}

so it is important that your model have just 4 properties (UserId, Debit, Credit and Balance). If you want to add extra properties, you should add it in FinanceofstudentModel and fill in controller.

I hope this help you

score -1 · Answer 3 · answered May 20 '17 at 22:39

Create a class:

public class StudentFinance
{
    public double debit { get; set; }
    public double credit { get; set; }
    public double balance { get; set; }
}

Change your LINQ query to

        var financeofstudents = db.Financeofstudents.Include(f => f.Batche).Include(f => f.User).GroupBy(x => x.UserId).Select(x => new StudentFinance
        {
            debit = x.Sum(item => item.debit),
            credit = x.Sum(item => item.credit),
            balance = x.Sum(item => item.balance)
        }).ToList();

Now finally, change model on your view to:

@model IEnumerable<StudentFinance>

That should work fine.. please mark answered if that solve your problem.

entity framework group by lambda expression is not working?

3 Answers3