We help IT Professionals succeed at work.

converting sql to linq not working

reddy999
reddy999 asked
on
265 Views
Last Modified: 2013-11-11
public static DataSet GetItemLists(int group_code)
        {
                          //converting following sql command to linq            
      //         string sql = "SELECT Distinct parent_sku, group_code, parent_sku, description "
      //                + "FROM item_view "
      //                + "WHERE group_code=@group_code ";

            using (WellsMusDataContext dc = new WellsMusDataContext())
           {

               List<item_view> query1 = (from item in dc.item_views
                                     where item.group_code == group_code
                                     select item).Distinct().ToList<item_view>();



               
              //this is my custom class that converts list to dataset
                CollectionToDataSet<List<item_view>> c = new CollectionToDataSet<List<item_view>>(query1);
                return c.CreateDataSet();
 
}
but it gives repeating data which is not distinct Can any body help me on this?
thank you
Comment
Watch Question

Anil GolamariFull Stack Developer
CERTIFIED EXPERT

Commented:
List<item_views> query1 = (from item in dc.item_views
                                     where item.group_code == group_code
                                     select item).ToList<item_view>();
list<item_view> distinctview = query1.distinct();

Can you try this query.
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
It is probably just this:
.ToList();

and you may need to cast it.

 List<item_view>  query1 = (from item in dc.item_views
                                     where item.group_code == group_code
                                     select item).Distinct().ToList();

Author

Commented:
Hi lucky85 thank you for reply i tried it getting below error

Error      3      'System.Collections.Generic.List<item_view>' does not contain a definition for 'distinct' and no extension method 'distinct' accepting a first argument of type 'System.Collections.Generic.List<item_view>' could be found (are you missing a using directive or an assembly reference?)      

Author

Commented:
hi amenkes it did not solved the problem yet can you explain cast furthur

Author

Commented:
Error      1      Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<item_view>' to 'System.Collections.Generic.List<item_view>'. An explicit conversion exists (are you missing a cast?)      i got above error now with the following code


 public static DataSet GetItemLists(int group_code)
        {
                          //string group_code
           
      //         string sql = "SELECT Distinct parent_sku, group_code, parent_sku, description "
      //                + "FROM item_view "
      //                + "WHERE group_code=@group_code ";

            using (WellsMusDataContext dc = new WellsMusDataContext())
           {

               List<item_view> query1 = (from item in dc.item_views
                                     where item.group_code == group_code
                                     select item).Distinct().ToList<item_view>();

 //this is my custom class that converts list to dataset


               List<item_view> distinctview = query1.Distinct();


               CollectionToDataSet<List<item_view>> c = new CollectionToDataSet<List<item_view>>(distinctview);
                return c.CreateDataSet();

 }
can anybody help me please
Anil GolamariFull Stack Developer
CERTIFIED EXPERT

Commented:
List<item_views> query1 = (from item in dc.item_views
                                     where item.group_code == group_code
                                     select item).ToList();
IEnumerable<string> ids = list.Select(item.group_code == group_code).Distinct();

can you try these two queries and see. If you have added the System.collections.generics then there should not be any reference's issue.

List<item_view> query1=  from item in dc.items..Cast<item_view>()  
                                          where item.group_code == group_code
                                          select item;
list<item_view> distinctview = query1.distinct();
Anil GolamariFull Stack Developer
CERTIFIED EXPERT

Commented:
https://www.experts-exchange.com/Programming/Languages/.NET/LINQ/Q_24079889.html

This link has a good amount of code you need as it is also dealing with list and distinct funtion in linq.
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
Right now, you need to do a groupby to get the distinct. Below is partial code. I am on a long, important call, so I will get the rest posted when done.
using System;
using System.Collections.Generic;
using System.Linq;

namespace WebApplicationC3
{
    class test
    {
        public string name;
    }
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            List<test> list = new List<test>();
            list.Add(new test() { name = "adam" });
            list.Add(new test() { name = "adam" });
            list.Add(new test() { name = "bob" });
            list.Add(new test() { name = "adam" });
            list.Add(new test() { name = "bob" });
            list.Add(new test() { name = "carl" });

            var newlist = (from t in list
                     select t).GroupBy(x => x.name).Distinct().ToList();
            List<test> otherList;
            
        }
     
    }
}

Open in new window

Author

Commented:
I think yes amenkes is correct i have to group by  to select didtinct. i will wait for the code.

thank you
C# ASP.NET Developer
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
thank you amenkes
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.