Geek asks and answers

Using Linq to Entity when retrieving rows from a huge database

I have a database containing billions of rows. I created a function that receives the number of parameters from the user and cuts the database with these parameters. This works well for me with a small DB (30,000 rows), but when I try to use this function in a large database, I got TIMEOUTEXCEPTION from SQLSERVER.

Here is my code:

  public static IQueryable<LogViewer.EF.InternetEF.Log> ExecuteInternetGetLogsQuery(FilterCriteria p_Criteria, ref GridView p_Datagrid)
        {


            IQueryable<LogViewer.EF.InternetEF.Log> internetQuery = null;

                using (InternetDBConnectionString context = new InternetDBConnectionString())
                {
                    internetQuery = context.Logs;
                    if ((p_Criteria.DateTo != null && p_Criteria.DateFrom != null))
                    {
                        internetQuery = internetQuery.Where(c => c.Timestamp >= p_Criteria.DateFrom && c.Timestamp < p_Criteria.DateTo);
                    }
                    else if (p_Criteria.DateFrom != null && p_Criteria.DateFrom > DateTime.MinValue)
                    {
                        internetQuery = internetQuery.Where(c => c.Timestamp >= p_Criteria.DateFrom);
                    }
                    else if (p_Criteria.DateTo != null && p_Criteria.DateTo > DateTime.MinValue)
                    {
                        internetQuery = internetQuery.Where(c => c.Timestamp < p_Criteria.DateTo);
                    }
                    if (!string.IsNullOrEmpty(p_Criteria.FreeText))
                    {
                        internetQuery = internetQuery.Where(c => c.FormattedMessage.Contains(p_Criteria.FreeText));
                    }

                    if (p_Criteria.Titles.Count > 0)
                    {
                        internetQuery = internetQuery.AsEnumerable().Where(c => p_Criteria.Titles.Contains(c.Title)).AsQueryable();
                    }
                    if (p_Criteria.MachineNames.Count > 0)
                    {
                        internetQuery = internetQuery.AsEnumerable().Where(c => p_Criteria.MachineNames.Contains(c.MachineName)).AsQueryable();
                    }
                    if (p_Criteria.Severities.Count > 0)
                    {
                        internetQuery = internetQuery.AsEnumerable().Where(c => p_Criteria.Severities.Contains(c.Severity)).AsQueryable();
                    }
                    internetQuery= internetQuery.OrderByDescending(c=>c.LogID);
                    if (internetQuery.Count() > p_Criteria.TopValue)
                    {
                        internetQuery = internetQuery.Take(p_Criteria.TopValue);
                    }
                    p_Datagrid.DataSource = internetQuery;
                    p_Datagrid.DataBind();
                    return internetQuery;

                }  
        }

My version of SQL is 2005. I got an exception in a row p_Datagrid.DataBind();.

Any suggestions? Thanks

+3
source share
6 answers

After a week of searching for a solution, I found this message. This works great with an indexed database with over a billion rows. Here is my code solution:

public static IQueryable<LogViewer.EF.InternetEF.Log> ExecuteInternetGetLogsQuery(FilterCriteria p_Criteria, ref GridView p_Datagrid)
        {


            IQueryable<LogViewer.EF.InternetEF.Log> internetQuery = null;
            List<LogViewer.EF.InternetEF.Log> executedList = null;
            using (InternetDBConnectionString context = new InternetDBConnectionString())
            {
                internetQuery = context.Logs;
                if ((p_Criteria.DateTo != null && p_Criteria.DateFrom != null))
                {
                    internetQuery = internetQuery.Where(c => c.Timestamp >= p_Criteria.DateFrom.Value && c.Timestamp < p_Criteria.DateTo.Value);
                }
                else if (p_Criteria.DateFrom != null && p_Criteria.DateFrom > DateTime.MinValue)
                {
                    internetQuery = internetQuery.Where(c => c.Timestamp >= p_Criteria.DateFrom);
                }
                else if (p_Criteria.DateTo != null && p_Criteria.DateTo > DateTime.MinValue)
                {
                    internetQuery = internetQuery.Where(c => c.Timestamp < p_Criteria.DateTo);
                }
                if (!string.IsNullOrEmpty(p_Criteria.FreeText))
                {
                    internetQuery = internetQuery.Where(c => c.FormattedMessage.Contains(p_Criteria.FreeText));
                }


                if (p_Criteria.Titles.Count > 0)
                {
                    internetQuery = internetQuery.Where(BuildOrExpression<LogViewer.EF.InternetEF.Log, string>(p => p.Title, p_Criteria.Titles));
                }
                if (p_Criteria.MachineNames.Count > 0)
                {
                    internetQuery = internetQuery.Where(BuildOrExpression<LogViewer.EF.InternetEF.Log, string>(p => p.MachineName, p_Criteria.MachineNames));
                }
                if (p_Criteria.Severities.Count > 0)
                {
                    internetQuery = internetQuery.Where(BuildOrExpression<LogViewer.EF.InternetEF.Log, string>(p => p.Severity, p_Criteria.Severities));
                }


                internetQuery = internetQuery.Take(p_Criteria.TopValue);
                executedList = internetQuery.ToList<LogViewer.EF.InternetEF.Log>();
                executedList = executedList.OrderByDescending(c => c.LogID).ToList<LogViewer.EF.InternetEF.Log>(); ;

                p_Datagrid.DataSource = executedList;

                p_Datagrid.DataBind();


                return internetQuery;

            }
        }



public static Expression<Func<TElement, bool>> BuildOrExpression<TElement, TValue>(
        Expression<Func<TElement, TValue>> valueSelector,
        IEnumerable<TValue> values )
        {
            if (null == valueSelector)
                throw new ArgumentNullException("valueSelector");

            if (null == values)
                throw new ArgumentNullException("values");

            ParameterExpression p = valueSelector.Parameters.Single();

            if (!values.Any())
                return e => false;

            var equals = values.Select(value =>
                (Expression)Expression.Equal(
                     valueSelector.Body,
                     Expression.Constant(
                         value,
                         typeof(TValue)
                     )
                )
            );

            var body = equals.Aggregate<Expression>(
                     (accumulate, equal) => Expression.Or(accumulate, equal)
             );

            return Expression.Lambda<Func<TElement, bool>>(body, p);
        }

,

+1

, :

  • (Bad idé )
  • linq.
  • . , .
  • , - , where, order by.
  • datagrid. ? , top 1000 top 10000. - .

.

, :

if (p_Criteria.Titles.Count > 0)
{
     internetQuery = internetQuery.AsEnumerable().Where(c => p_Criteria.Titles.Contains(c.Title)).AsQueryable();
}
if (p_Criteria.MachineNames.Count > 0)
{
      internetQuery = internetQuery.AsEnumerable().Where(c => p_Criteria.MachineNames.Contains(c.MachineName)).AsQueryable();
}
if (p_Criteria.Severities.Count > 0)
{
      internetQuery = internetQuery.AsEnumerable().Where(c => p_Criteria.Severities.Contains(c.Severity)).AsQueryable();
}

IEnumerable , where . , , . , , contains IQueryable. IQueryable .

+2

, .

  • . , -. , SP Entity, this

  • 1. , / . SQL Server, this

  • "" . , , .

: @Arion , .

+1

, " " (, SQL) - , /. , , ( Take(1000) , .

Titles, MachineNames Severities AsEnumerable(), , 3 , SQL, , , 3 , 3 .

, AsEnumerable() 3 ? ( AsQueryable())?

, /DbContext '* ConnectionString' '* Context'? , , :)

+1

, . ? - , , . 100 , ?

0
internetQuery = internetQuery.AsEnumerable().Where(c => p_Criteria.Severities.Contains(c.Severity))

EF , , SELECT N + 1?

Otherwise, you may be missing a pointer to something.

I would check the generated SQL first.

0
source

More articles:


All Articles