Reading from HUGE database table

Question

I am using LINQ to access my database tables. Some of the tables might have (hundreds of thousands) of records.

Reading from the table using a statement like this:

var records = db.Logs;

will make the application very very slow. It will load all records at once.

I need to filter the result with more than a condition, like:

if (UserID != null)
{
    records = records.Where(r => r.User == UserID);
}

if (UserIP != null)
{
    records = records.Where(r => r.IP   == UserIP);
}

The problem is that my first read from the table will bring me all the records and will make the application very slow.

Is there any way to make conditions in the LINQ statement (if, switch) as we used to do with SQL Statements?

This is how old logic used to be created:

string sql = "SELECT * FROM Log WHERE 1=1";
if (UserID != null)
{
    sql += " AND User = '" + UserID + "'";
}

if (UserIP != null)
{
    sql += " AND IP = '" + UserIP + "'";
}

sqlCmd.query(sql);

Answer 1

Actually an assignment like:

  var records = db.Logs;

does not execute the query immediatly. It only prepares a datastructure to be used later. The execution of the query only happens when the code needs the data, and any where clauses are integrated in the query, preventing it from returning the whole table.

It is perfectly possible to do stuff like this:

  var records = db.Logs;

  if (filter1) records = records.Where(r => r.Field1 == condition1);
  if (filter2) records = records.Where(r => r.Field2 == condition2);

This will end in executing one query, with a (kind of) dynamic where statement.

You should define the correct indexes however.