List <T> FirstOrDefault () Poor performance - is a dictionary possible in this case?

Question

I have a set of Z "codes" that are valid for a certain period of time.

Since I need them many times in a large loop (million +), and every time I have to look for the appropriate code, I cache them in List <>. After finding the correct codes, I insert (using SqlBulkCopy) a million rows.

I am looking at an identifier with the following code ( l_zis List<T>)

var z_fk = (from z in l_z
            where z.CODE == lookupCode &&
                  z.VALIDFROM <= lookupDate &&
                  z.VALIDUNTIL >= lookupDate 
            select z.id).SingleOrDefault();

In other situations, I used a dictionary with excellent performance, but in those cases I only had to search for an identifier based on code.

But now when you try to find a combination of fields, I'm stuck.

Any ideas? Thanks in advance.

Answer 1

, - Dictionary<string, List<Code>> (, , Code).

, lookupDate, dict[lookupCode]:

var z_fk = (from z in dict[lookupCode]
            where z.VALIDFROM <= lookupDate &&
                  z.VALIDUNTIL >= lookupDate 
            select z.id).SingleOrDefault();

, , Code, List<Code> dict, lookupCode ( , ).

Answer 2

...

//in initialization somewhere
ILookup<string, T> l_z_lookup = l_z.ToLookup(z=>z.CODE);

//your repeated code:
var z_fk = (from z in lookup[lookupCode]
            where z.VALIDFROM <= lookupDate && z.VALIDUNTIL >= lookupDate 
            select z.id).SingleOrDefault();

, , , , . , SqlBulkCopy - , , , , CODE, VALIDUNTIL VALIDFROM.

Lookup a Dictionary, Lists, API (, ).

Answer 3

, . , .

Answer 4

, - , .

? (, time_t). , . .

SQL ? .

, - .

- . , , .

- . ...

- > - , N/M ( N = M = ). , 100 . , . . , . (, , , ).

, , , , , . ? . , . . .

, ( , ). , . , , 12:00 12:01, .. ( ) , .

? . , . , . . , SQL .