Retrieve and cache a result set into my Application

Question

I have a byte[] column in a table that has fingerprint data stored in it. I wish to query the rows from a table only once and store the record set in a variable or somewhere in my code, so that I don't have to query the database each and every time. The query will return thousands of rows.

This will fetch all the records for me:

var table = (from a in context.tblFingerprints
                              select new {a} ).ToList();

I tried declaring a variable in AppData class: public List<object> TableData; Then tried to store the variable 'table' values to it.

Data.TableData = table;

The error remains:

Cannot implicitly convert type 'System.Collections.Generic.List<<anonymous type: FingerprintTEST.tblFingerprint a>>' to 'System.Collections.Generic.List<object>'

This is how I wish to query the rows returned from the result for matching fingerprint:

foreach (var row in Data.TableData)
{
    Template tem = new Template();
    tem.DeSerialize(row.a.fingerTemplate);

    if (tem != null)
    {
        // Compare feature set with particular template.
        Verificator.Verify(features, tem, ref res);

        if (res.Verified)
        {...}
    }
}

Any ideas please?

Databases are designed to be queried, have you run into a performance issue? — TheGeneral, Feb 19 '20 at 00:55
@MichaelRandall Well, as my database grows it takes much more time to retrieve and compare one fingerprint against thousands of records. — DevRingim, Feb 19 '20 at 09:47

score 0 · Answer 1 · answered Feb 19 '20 at 00:55

0

You are returning these as new objects with select new {a}. If context.tblFingerprints is of type TableData you just need to select a

var table = (from a in context.tblFingerprints
                          select a).ToList();

answered Feb 19 '20 at 00:55

chrisbyte

1,408
3
11
18

score 0 · Answer 2 · answered Feb 19 '20 at 00:55

You don't need the select new { a } (this is creating a new anonymous-type, with a single member for a whole record, which is silly.
- You also don't need any Linq expression at all, just use ToList() on the DbSet directly.
Store the result in a static variable.

class Something
{
    private static List<tblFingerprint> _fingerprints;

    public void Do()
    {
        DbContext context = ...

        if( _fingerprints is null )
        {
            _fingerprints = context.tblFingerprints.ToList();
        }

        // do stuff with `_fingerprints`
    }
}

score 0 · Answer 3 · answered Feb 19 '20 at 00:57

0

Remove the "new {a}" and replace with just "a", and tell ToList that it's a list of objects.

var table = (from a in context.tblFingerprints
             select a).ToList<object>();

answered Feb 19 '20 at 00:57

BrianM

836
9
17

Retrieve and cache a result set into my Application

3 Answers3