C# - LINQ - specific query select

Question

I would like to get a specific select with the best performance possible

I currently have a code like this:

var getData = from x in dbContex.MyTable
                                            where x.Car == "audi"
                                            select x;

MyTable has three columns: Car ProductionYear Color

I would like to download a specific vehicle brand with the year of manufacture that has a specific color.

I am currently doing it with reference to the code above:

 foreach (var item in getData)
                {
                    if (item.color == "blue") item.ProductionYear = "1999";
                    if (item.color == "red") item.ProductionYear = "2003";

        // etc (...)
                }

I would like to assign a specific year for a given car and color if I find it.

A very prosaic example above.

Problem - I currently have 100 colors and only want to check 10 colors.

How can I do it faster so as not to search the entire list and not to use IF statements?

LINQ is not designed to change anything. As I see it, you want to change the year of an element depending on what colour it has — Red Star
– Red Star, Commented Aug 13, 2022 at 18:54
Crate an array of the 10 colors you want. Then check if color is contained in the array : if(myColorArray.Contains(item.color) && if(item.ProductionYear == "1999")) — jdweng
– jdweng, Commented Aug 13, 2022 at 19:02

Moho · Accepted Answer · 2022-08-13 19:20:43Z

2

Create an array of the colors, then use Contains method in the predicate expression:

var colors = new [] { "red", "blue" };

var results = dbContext.MyTable
    .Where( x => "audi" == x.Car
        && colors.Contains( x.color ) );

Then use a switch statement to assign your years:

foreach(var car in results)
{
    car.ProductionYear = car.color switch
    {
        "blue" => 1999,
        "red" => 2003,
        _ => // unexpected result, throw exception
    }
}

You could also define the years with the colors then work the year assignment into the LINQ query:

var colorYears = new []
{
    new {
        color = "red",
        ProductionYear = 2003,
    },
    ...
};

var colors = colorYears.Select( x => x.color ).ToArray();

var results = (...query from above...)
    // enumerate
    .ToArray()
    // join datasource results with your colorYears array
    // on the `color` property
    .Join( colorYears, 
        c => c.color, 
        cy => cy.color,
        ( c, cy ) => 
        {
            // assign the configured production year for the color
            c.ProductionYear = cy.ProductionYear;
            // return the entity
            return c;
        } );

edited Aug 13, 2022 at 19:20

answered Aug 13, 2022 at 18:54

Moho

16.8k1 gold badge36 silver badges35 bronze badges

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

4 Comments

Red Star Over a year ago

Don`t you think that way will have much more comparisons rather that switch withot filtering?

TomProjectID_23 Over a year ago

@moho I specified a class with colors and I refer to it in a switch statement, but I get the error "A constant value is expected" I do not want to hardly enter the color but refer to the place where I already have them defined

Moho Over a year ago

@TomProjectID_23 as the error stated, you need to use constants for the cases in the switch statement/expression. I updated my answer earlier with such a scenario using the Join method to return the entities with the updated ProductionYear properties

TomProjectID_23 Over a year ago

@Moho - yes, everything is fine, I was wondering how to bypass the fixed color assignment - I asked the last question in the new post below - will you take a look?

Red Star · Accepted Answer · 2022-08-13 19:01:30Z

0

There is no scenario, where you don`t use if or switch clause. You want to change data, which is not LINQ case. Only thing I would do - go to switch, rather than If clause.

answered Aug 13, 2022 at 19:01

Red Star

6774 silver badges15 bronze badges

2 Comments

juharr Over a year ago

This is not true, you can create a dictionary that maps color to a year and use that instead of if statements or switches.

Red Star Over a year ago

@juharr, hm, really. I think, much better to say is "There is no scenario, where you don`t iterate".

TomProjectID_23 · Accepted Answer · 2022-08-14 09:14:27Z

0

I don't want to create a new thread so I will write here - a question about LINQ of course and data comparison in the array

I have this code:

 var dataTest = (dbContex.MyTable.Where(x => x.col1 == str1
          && x.col2 == str2 && x.col3 == str3).Select(x => x.ID ).FirstOrDefault());

I want to check if there is such a row for these three fields and get its ID - everything is fine when I have values, but when any field is NULL - it cannot compare it and returns information that there is no such record.

the goal is to check if there is a duplicate with these values in MyTable based on these three fields - except that each of these fields can be NULL

how best to compare NULL?

answered Aug 14, 2022 at 9:14

TomProjectID_23

436 bronze badges

Collectives™ on Stack Overflow

C# - LINQ - specific query select

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related