C# LINQ Select table from list with multiple fields

Question

I'd like to select many values from a table with a list. I have FabricTable(Year is int):

+----+-------+---------+------+
| Id | Color | Texture | Year |
+----+-------+---------+------+
| 1  | Red   | Rough   | 2019 |
+----+-------+---------+------+
| 2  | Green | Soft    | 2019 |
+----+-------+---------+------+
| 3  | Blue  | Rough   | 2019 |
+----+-------+---------+------+
| 4  | Red   | Med     | 2019 |
+----+-------+---------+------+
| 5  | Blue  | Soft    | 2018 |
+----+-------+---------+------+

I have selectedItems list (year is int):

+---------+------+
| Texture | Year |
+---------+------+
| Rough   | 2019 |
+---------+------+
| Soft    | 2019 |
+---------+------+

I'd like to get the Id from table, it should result with Id = 1, 2, & 3.

How can I achieve this with Linq in C#? I just need to select by Texture & Year

Here's what I've tried but I'm not sure how to select from list with multiple values(selectedItems is a list but I don't know how to query multiple columns):

db.FabricTable.Select(o => o.Texture == selectedItems.Texture && o.Year == selectItems.Year)

what did you try? what did not work? you need to show a bit of what is wrong before asking a question. — Bruno Belmondo
– Bruno Belmondo, Commented Jul 19, 2019 at 16:06
@BrunoBelmondo I've added what I tried, but I get red squiggly line on selectedItems.Texture and selectedItems.Year — sojim2
– sojim2, Commented Jul 19, 2019 at 16:12
A Where clause will select items from the table, and each item will contain all the properties, including Id. For filtering the items based on a list, see Andy's answer below. — littlerufe
– littlerufe, Commented Jul 19, 2019 at 17:15

Andy · Accepted Answer · 2019-07-19 16:43:30Z

2

You get a compiler error when using selectedItems.Texture because selectedItem is a list that contains an object with the Texture property. You need to check all of the items in the list when searching for the desired items in FabricTable:

var items = db.FabricTable.Where(o => selectedItems.Any(selectedItem => o.Texture == selectedItem.Texture && o.Year == selectedItem.Year));

answered Jul 19, 2019 at 16:43

Andy

30.5k7 gold badges81 silver badges90 bronze badges

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

5 Comments

sojim2 Over a year ago

This query results in 1 items for me, when it should result with many. I guess I should encapsulate this query in foreach

littlerufe Over a year ago

No, you shouldn't need a foreach. This will select the first three rows.

sojim2 Over a year ago

@RufusL Here you can see the selectedItems has 4 count, but the itemsonly has 1, I've verified in the database that there are 4 items to remove. i.imgur.com/XxJ1V6P.gif

littlerufe Over a year ago

That data is not the same as what you've shown above. You are only selecting values from the first item, and none of the others have exact matches on all three of those properties.

sojim2 Over a year ago

It was my mistake, I had one of the variables wrong. Thank you!

jdweng · Accepted Answer · 2019-07-19 16:14:38Z

0

Try following :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;


namespace ConsoleApplication120
{
    class Program
    {
        static void Main(string[] args)
        {
            List<Item> items = new List<Item>() { 
                new Item() { Id = 1, Color = "Red", Texture = "Rough",  Year = 2019},
                new Item() { Id = 2, Color = "Green", Texture = "Soft",  Year = 2019},
                new Item() { Id = 3, Color = "Blue", Texture = "Rough",  Year = 2019},
                new Item() { Id = 4, Color = "Red", Texture = "Soft",  Year = 2018}
            };
            DataTable dt = new DataTable();
            dt.Columns.Add("Color", typeof(string));
            dt.Columns.Add("Texture", typeof(string));
            dt.Columns.Add("Year", typeof(int));

            foreach (Item item in items.Where(x => (x.Texture == "Rough") && (x.Year == 2019)))
            {
                dt.Rows.Add(new object[] { item.Color, item.Texture, item.Year });
            }

        }

    }
    public class Item
    {
        public int Id { get; set; }
        public string Color { get; set; }
        public string Texture { get; set; }
        public int Year { get; set; }
    }

}

answered Jul 19, 2019 at 16:14

jdweng

34.5k3 gold badges17 silver badges19 bronze badges

5 Comments

sojim2 Over a year ago

I don't want to search by hard coded values but by the fields in list, I've edited the OP to be clear on my intent

jdweng Over a year ago

so just change to a variable.

sojim2 Over a year ago

The table has the Id not the list, I'm trying to get the Id from the table with the list I have.

jdweng Over a year ago

The list has the Id, not the table. If you do not put the Id into the table you can not get it.

littlerufe Over a year ago

I read the question as "I have a database table that I'd like to use as a datasource for a list", although the OP is fairly unclear because the title says the opposite.

Collectives™ on Stack Overflow

C# LINQ Select table from list with multiple fields

2 Answers 2

5 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related