Solved

easy LINQ -- ASP.Net MVC 4 C#

Posted on 2013-06-12
15
1,098 Views
Last Modified: 2013-07-01
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.Infrastructure.DbQuery`1[<>f__AnonymousType5`1[System.String]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable
            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<MvcApplication21.Models.POheader>
<table>
@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Supplier)
        </td>
    </tr>
}
</table>
0
Comment
Question by:finance_teacher
  • 7
  • 5
  • 3
15 Comments
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39242596
Hi,

Can you try the following?
var myPOs = (from p in db.POheaders
.GroupBy(x=x.Supplier)
.Select(x=>x.Key);

Open in new window


If it doesn't work, post the error message.

Giannis
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39243319
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.

var myPOs = (from p in db.POheaders
             select p).Distinct();

Open in new window

           
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<POheaders>. 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<TSource> Method
0
 

Author Comment

by:finance_teacher
ID: 39244224
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
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39244237
I had some typos:

var myPOs = from p in db.POheaders
.GroupBy(x=>x.Supplier)
.Select(x=>x.Key);

Open in new window


Giannis
0
 

Author Comment

by:finance_teacher
ID: 39244625
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);
        }
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39244661
I mistakenly thought that .Select was all that was needed....

        public ActionResult IndexMgr()
        {
            var myPOs = 
                         from p in db.POheaders
                         .GroupBy(x=>x.Supplier)
                         .Select(x=>x.Key) select p;
                         //return View(myPOs);
        }

Open in new window


Giannis
0
 

Author Comment

by:finance_teacher
ID: 39244715
Below runs, but displays "The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbQuery`1[System.String]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[MvcApplication21.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<MvcApplication21.Models.POheader>

@foreach (var item in Model) {
            @Html.DisplayFor(modelItem => item.Supplier)
}
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39244723
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
 var myPOs = 
                         from p in db.POheaders
                         .GroupBy(x=>x.Supplier)
                         .Select(x=>x.Key);
                         //return View(myPOs);

Open in new window

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);

Open in new window

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.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39244735
Can you describe POheaders class?
0
 

Author Comment

by:finance_teacher
ID: 39244784
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 ?
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39244808
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...
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39244838
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.
0
 

Author Comment

by:finance_teacher
ID: 39244839
Below is my MODEL class that gets autogenerated.
Do you have an easy solution ?

using System.ComponentModel.DataAnnotations;
//------------------------------------------------------------------------------
// <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; }

    }
}
0
 

Accepted Solution

by:
finance_teacher earned 0 total points
ID: 39277979
0
 

Author Closing Comment

by:finance_teacher
ID: 39289369
found easy solution
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now