C# LINQ query from datatable

Question

I'm trying to implement a LINQ query on a search page we're working with. The data coming back from the initial query is laid out as follows:

ListID ListName TagId TagValue
1      Name1    1     Tag1
1      Name1    2     Tag2
2      Name2    3     Tag3

Basically the lists are search categories and the tags are search terms. What I'm trying to do is bring back this list, separate it into unique lists, and then display the related tags on the page.

I have the data loaded into a dataTable, now I'm trying to get the list names into my SearchCategories object:

public class SearchCategories
{
    public int SearchCategoryId { get; set; }

    public string SearchCategoryName { get; set; }

    public SearchTags searchTags { get; set; }

    public List<SearchTags> lstSearchTags= new List<SearchTags>();
}

and the SearchTag class:

public class SearchTags
{
    public int SearchTagId { get; set; }

    public string SearchTagValue { get; set; }
}

and then the tags into the object's related list.

Here's the code I'm working with:

dtLists.Load(reader);

var distinctValues = dtLists.AsEnumerable()
   .Select(row => new SearchCategories
   {
   SearchCategoryId = row.Field<int>("ListNameID"),
   SearchCategoryName = row.Field<string>("listName")
   })
   .Distinct();

Newer to LINQ but think this should be easier. Thanks in advance.

Take a look at GroupBy (instead of Distinct).

Ivan Stoev
– Ivan Stoev

2016-12-19 15:26:34 +00:00
Commented Dec 19, 2016 at 15:26 — Ivan Stoev
– Ivan Stoev, Commented Dec 19, 2016 at 15:26

Roland Halbaksz · Accepted Answer · 2016-12-19 16:07:54Z

2

This will do the job:

var result = dtLists.AsEnumerable()
        .GroupBy(m => new
        {
            ListID = m.Field<int>("ListID"),
            ListName = m.Field<string>("ListName")
        }, m => m)
        .Select(m => new
        {
            m.Key.ListID,
            m.Key.ListName,
            Tags = m.Select(x => new { x.Field<int>("TagId"), x.Field<string>("TagValue") })
        });

Then of course you have to map to your type...

answered Dec 19, 2016 at 16:07

Roland Halbaksz

1571 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Magnus · Accepted Answer · 2016-12-19 16:19:33Z

1

This should be fairly easy to do using GroupBy.

dtLists.AsEnumerable()
    .GroupBy(x => new { ListNameID = x.Field<int>("ListNameID"), ListName = x.Field<string>("ListName") })
    .Select(x => new SearchCategories()
    {
        SearchCategoryId = x.Key.ListNameID,
        SearchCategoryName = x.Key.ListName,
        lstSearchTags = x.Select(y => new SearchTags(){ SearchTagId = y.Field<int>("TagId"), SearchTagValue = y.Field<string>("TagValue") }).ToList()
    });

edited Dec 19, 2016 at 16:19

answered Dec 19, 2016 at 16:10

Magnus

47.3k8 gold badges88 silver badges128 bronze badges

Comments

Zinov · Accepted Answer · 2016-12-19 16:03:27Z

I am not clear how you want your output, based on which criteria, just ask if you want to be more precise.

void Main()
{
    var list = new List<Data>(){
                            new Data
                            {
                               ListId = 1,
                               ListName = "Name1",
                               TagId = 1,
                               TagValue = "Tag1"
                            },
                            new Data
                            {
                               ListId = 1,
                               ListName = "Name1",
                               TagId = 2,
                               TagValue = "Tag2"
                            },
                            new Data
                            {
                               ListId = 2,
                               ListName = "Name2",
                               TagId = 3,
                               TagValue = "Tag3"
                            }};

    //this is to group all SearchCategories by SearchCategoryName
    var res = list.Select(x => new SearchCategories
    {
       SearchCategoryId = x.ListId,
       SearchCategoryName = x.ListName
    }).GroupBy(x => x.SearchCategoryName);

    //to have just distinct categories one time
    var res2 = list.Select(x => new SearchCategories
    {
        SearchCategoryId = x.ListId,
        SearchCategoryName = x.ListName
    }).Distinct(new SearchCategoriesComparer());

    //display your data here based on a Key. The output should be Key=Name1  with two elements inside and the other one with one element Key=Name2
    res.Dump(); 

    //display your data here
    res2.Dump(); 

}

public class Data
{
    public int ListId   {get; set;}
    public string ListName { get; set; }
    public int TagId { get; set; }
    public string TagValue { get; set;}
}


public class SearchCategories
{
    public int SearchCategoryId { get; set; }

    public string SearchCategoryName { get; set; }

    public List<SearchTags> lstSubCategories = new List<SearchTags>();
}

public class SearchCategoriesComparer : IEqualityComparer<SearchCategories>
{
    public bool Equals(SearchCategories x, SearchCategories y)
    {
        return x.SearchCategoryName == y.SearchCategoryName;
    }

    public int GetHashCode(SearchCategories obj)
    {
        return obj.SearchCategoryName.GetHashCode();
    }
}


public class SearchTags
{
    public string Tag{ get; set;}
}

Kevin Smith · Accepted Answer · 2016-12-19 16:16:28Z

1

try something like the following, uses the GroupBy, Not sure what your SearchTags so replace x => new SearchTags(x) with how its constructed.

dtLists.AsEnumerable()
    .Select(row => new {
       SearchCategoryId = row.Field<int>("ListID "),
       SearchCategoryName = row.Field<string>("listName"),
       TagId = row.Field<int>("TagId"),
       TagValue = row.Field<string>("TagValue")
    })
    .GroupBy(x => x.SearchCategoryId)
    .Select(g => new SearchCategories() {
                SearchCategoryId  = g.Key,
                SearchCategoryName = g.First().SearchCategoryName,
                lstSubCategories = g.Select(x => new SearchTags(){ SearchTagId = x.TagId, SearchTagValue = x.TagValue}).ToList()
                }).ToList();

edited Dec 19, 2016 at 16:16

answered Dec 19, 2016 at 16:00

Kevin Smith

14.6k5 gold badges63 silver badges88 bronze badges

2 Comments

TrevorGoodchild Over a year ago

This is closest to what I've been trying. I updated the question with the search tags property, but I'm not constructing it correctly.

Kevin Smith Over a year ago

I've updated the answer with the new construction of SearchTags

Collectives™ on Stack Overflow

C# LINQ query from datatable

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related