finance_teacher
asked on
easy LINQ -- ASP.Net MVC 4 C#
How can I fix the below so it displays just test1, test3, and test5 ?
Currently it displays all seven POheaders database records
test1 = 3 times
test3 = 1 time
test5 = 3 times
I want it to work like the below SQL.
select distinct supplier
from POheader
-------------------------- ---------- ---------- ------
POheaderController.cs
public ActionResult IndexMgr()
{
var myPOs =
(from p in db.POheaders
// below works, but displays all records since AutoID is always unique, I just want to display
// select p
//below fails with The model item passed into the dictionary is of type 'System.Data.Entity.Infras tructure.D bQuery`1[< >f__Anonym ousType5`1 [System.St ring]]', but this dictionary requires a model item of type 'System.Collections.Generi c.IEnumera ble
select new {p.Supplier}
//below also fails with the same error message
//select p.Supplier
//below always works
)
.Distinct();
return View(myPOs);
}
-------------------------- ---------- ---------- ------
IndexMgr.cshtml -- LIST
@model IEnumerable<MvcApplication 21.Models. POheader>
<table>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.Supplier)
</td>
</tr>
}
</table>
Currently it displays all seven POheaders database records
test1 = 3 times
test3 = 1 time
test5 = 3 times
I want it to work like the below SQL.
select distinct supplier
from POheader
--------------------------
POheaderController.cs
public ActionResult IndexMgr()
{
var myPOs =
(from p in db.POheaders
// below works, but displays all records since AutoID is always unique, I just want to display
// select p
//below fails with The model item passed into the dictionary is of type 'System.Data.Entity.Infras
select new {p.Supplier}
//below also fails with the same error message
//select p.Supplier
//below always works
)
.Distinct();
return View(myPOs);
}
--------------------------
IndexMgr.cshtml -- LIST
@model IEnumerable<MvcApplication
<table>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem
</td>
</tr>
}
</table>
Hi finance_teacher;
In order for Distinct method to return only one record for each multiple records that the query is retuning all columns in the records must be the same. But because AutoID is causing them to be NOT Distinct all the records of each type that have multiple records are being returned.
In order to correct this you need to use the overload of the Distinct method that takes as a parameter a IEqualityComparer<TSource> or in this case a IEqualityComparer<POheader s>. The documentation has an example of how to implement it. When doing this do NOT Compare AutoID field. You only want to comapre the fields that will make the records Distinct.
Enumerable.Distinct<TSourc e> Method
In order for Distinct method to return only one record for each multiple records that the query is retuning all columns in the records must be the same. But because AutoID is causing them to be NOT Distinct all the records of each type that have multiple records are being returned.
var myPOs = (from p in db.POheaders
select p).Distinct();
In order to correct this you need to use the overload of the Distinct method that takes as a parameter a IEqualityComparer<TSource>
Enumerable.Distinct<TSourc
ASKER
jyparaskPosted, I get the attached errors when doing your below.
public ActionResult IndexMgr()
{
var myPOs = (from p in db.POheaders
.GroupBy(x=x.Supplier)
.Select(x=>x.Key);
}
-------------------------- ---------- ---------- -----
FernandoSotoPosted, I tested your solution, but cannot get it working.
Can you provide an easy example ?
I was thinking about using something really easy like
http://code.msdn.microsoft.com/LINQ-to-DataSets-Grouping-c62703ea
var myPOs = (from p in db.POheaders
group p by p.Supplier); etc ??
return View(myPOs);
s024.jpg
public ActionResult IndexMgr()
{
var myPOs = (from p in db.POheaders
.GroupBy(x=x.Supplier)
.Select(x=>x.Key);
}
--------------------------
FernandoSotoPosted, I tested your solution, but cannot get it working.
Can you provide an easy example ?
I was thinking about using something really easy like
http://code.msdn.microsoft.com/LINQ-to-DataSets-Grouping-c62703ea
var myPOs = (from p in db.POheaders
group p by p.Supplier); etc ??
return View(myPOs);
s024.jpg
I had some typos:
Giannis
var myPOs = from p in db.POheaders
.GroupBy(x=>x.Supplier)
.Select(x=>x.Key);
Giannis
ASKER
Below errors with "A query body must end with a select clause or a group clause".
How can I fix ?
public ActionResult IndexMgr()
{
var myPOs =
from p in db.POheaders
.GroupBy(x=>x.Supplier)
.Select(x=>x.Key);
//return View(myPOs);
}
How can I fix ?
public ActionResult IndexMgr()
{
var myPOs =
from p in db.POheaders
.GroupBy(x=>x.Supplier)
.Select(x=>x.Key);
//return View(myPOs);
}
I mistakenly thought that .Select was all that was needed....
Giannis
public ActionResult IndexMgr()
{
var myPOs =
from p in db.POheaders
.GroupBy(x=>x.Supplier)
.Select(x=>x.Key) select p;
//return View(myPOs);
}
Giannis
ASKER
Below runs, but displays "The model item passed into the dictionary is of type 'System.Data.Entity.Infras tructure.D bQuery`1[S ystem.Stri ng]', but this dictionary requires a model item of type 'System.Collections.Generi c.IEnumera ble`1[MvcA pplication 21.Models. POheader]" when going to the below IndexMgr.cshtml code.
How can I fix IndexMgr.cshtml ?
public ActionResult IndexMgr()
{
var myPOs =
from p in db.POheaders
.GroupBy(x => x.Supplier)
.Select(x => x.Key)
select p;
return View(myPOs);
}
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ------
IndexMgr.cshtml
@model IEnumerable<MvcApplication 21.Models. POheader>
@foreach (var item in Model) {
@Html.DisplayFor(modelItem => item.Supplier)
}
How can I fix IndexMgr.cshtml ?
public ActionResult IndexMgr()
{
var myPOs =
from p in db.POheaders
.GroupBy(x => x.Supplier)
.Select(x => x.Key)
select p;
return View(myPOs);
}
--------------------------
IndexMgr.cshtml
@model IEnumerable<MvcApplication
@foreach (var item in Model) {
@Html.DisplayFor(modelItem
}
Hi finance_teacher;
The solution suggested by jyparask will throw an exception because the View is expecting a collection of IEnumerable<POheader> but the solution is returning a collection of IEnumerable<Supplier>.
To your last post this code
You stated that you wanted to use the group by clause to get around implementing the IEqualityComparer, what column or group of columns will you want to group on? You can't group on Supplier because Supplier records will have its own ID column that will make them unique and therefore you will have one group for each record.
The solution suggested by jyparask will throw an exception because the View is expecting a collection of IEnumerable<POheader> but the solution is returning a collection of IEnumerable<Supplier>.
To your last post this code
var myPOs =
from p in db.POheaders
.GroupBy(x=>x.Supplier)
.Select(x=>x.Key);
//return View(myPOs);
is seen by the compiler as follows: var myPOs =
from p in db.POheaders.GroupBy(x=>x.Supplier).Select(x=>x.Key);
//return View(myPOs);
The issue is that the .Select(...) in the query is considered part of the db.POheaders ... and there is no select clause as part of the from clause of the query which is required.You stated that you wanted to use the group by clause to get around implementing the IEqualityComparer, what column or group of columns will you want to group on? You can't group on Supplier because Supplier records will have its own ID column that will make them unique and therefore you will have one group for each record.
Can you describe POheaders class?
ASKER
I want basic/simple LINQ to just display the distinct supplier column on my IndexMgr.cshtml page and am using http://www.sqltolinq.com to do the convertion since I always do SQL, but am just starting LINQ.
How can I easily fix so the above works on on my IndexMgr.cshtml page ?
How can I easily fix so the above works on on my IndexMgr.cshtml page ?
I think changing your model to accept the returned values would be the way, since you only need these distinct values. But i am not an expert in MVC, so...
Hi finance_teacher;
What query did Linqer come up with?
If the View will NOT be using anything else then the results of the Supplier table then you should change the View so that it expects a collection of Supplier and NOT a collection POheaders.
What query did Linqer come up with?
If the View will NOT be using anything else then the results of the Supplier table then you should change the View so that it expects a collection of Supplier and NOT a collection POheaders.
ASKER
Below is my MODEL class that gets autogenerated.
Do you have an easy solution ?
using System.ComponentModel.Data Annotation s;
//------------------------ ---------- ---------- ---------- ---------- ---------- ----
// <auto-generated>
// This code was generated from a template.
//
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------ ---------- ---------- ---------- ---------- ---------- ----
namespace MvcApplication21.Models
{
using System;
using System.Collections.Generic ;
public partial class POheader
{
public POheader()
{
this.POdetails = new HashSet<POdetail>();
}
public int ID { get; set; }
public string Supplier { get; set; }
public string LongName { get; set; }
public string SABVarChar { get; set; }
public virtual ICollection<POdetail> POdetails { get; set; }
}
}
Do you have an easy solution ?
using System.ComponentModel.Data
//------------------------
// <auto-generated>
// This code was generated from a template.
//
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------
namespace MvcApplication21.Models
{
using System;
using System.Collections.Generic
public partial class POheader
{
public POheader()
{
this.POdetails = new HashSet<POdetail>();
}
public int ID { get; set; }
public string Supplier { get; set; }
public string LongName { get; set; }
public string SABVarChar { get; set; }
public virtual ICollection<POdetail> POdetails { get; set; }
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
found easy solution
Can you try the following?
Open in new window
If it doesn't work, post the error message.
Giannis