public static async Task<Int32> CountAsync(this DbSet<Contact> dbSet, ContactFilter filter) { const String query = @"SELECT CAST(COUNT(*) AS INT) FROM c.Contact WHERE {0}"; String where = CreateWhere(filter); String curQuery = String.Format(query, where); ICollection<SqlParameter> parameters = CreateParameters(filter); return await GitTimeContext.GetContext(dbSet).Database.SqlQuery<Int32>(curQuery, parameters.ToArray()).FirstOrDefaultAsync(); }
private static String CreateWhere(ContactFilter filter) { var where = new StringBuilder("1 = 1"); if (!String.IsNullOrEmpty(filter.Subtype)) where.Append(" AND Contact.Subtype = @Subtype"); if (!String.IsNullOrEmpty(filter.Name)) where.Append(" AND Contact.Name LIKE @Name"); if (!String.IsNullOrEmpty(filter.PersonName)) where.Append(" AND (Contact.FirstName + ' ' + Contact.LastName LIKE @PersonName OR Contact.LastName + ', ' + Contact.FirstName LIKE @PersonName)"); return where.ToString(); }
public static async Task<ICollection<ContactFinderRow>> SelectFinderRowsAsync(this DbSet<Contact> dbSet, Int32 startRow, Int32 endRow, ContactFilter filter, String sortExpression) { const String query = @" WITH Contact AS ( SELECT Contact.pk_ID AS ContactID ,Contact.Name ,Contact.Email ,Contact.FirstName ,Contact.LastName ,Contact.Subtype ,ROW_NUMBER() OVER (ORDER BY {1}) AS RowNumber FROM c.Contact WHERE {0} ) SELECT * FROM Contact WHERE RowNumber BETWEEN @StartRow AND @EndRow ORDER BY RowNumber "; if (String.IsNullOrEmpty(sortExpression)) sortExpression = "ContactID"; String rowNumberOrderBy = sortExpression .Replace("ContactID", "Contact.pk_ID") ; String where = CreateWhere(filter); String curQuery = String.Format(query, where, rowNumberOrderBy); ICollection<SqlParameter> parameters = CreateParameters(filter); GitTimeContext.AddParameter("@StartRow", SqlDbType.Int, startRow, parameters); GitTimeContext.AddParameter("@EndRow", SqlDbType.Int, endRow, parameters); return await GitTimeContext.GetContext(dbSet).Database.SqlQuery<ContactFinderRow>(curQuery, parameters.ToArray()).ToListAsync(); }
private static ICollection<SqlParameter> CreateParameters(ContactFilter filter) { var parameters = new List<SqlParameter>(); if (!String.IsNullOrEmpty(filter.Subtype)) GitTimeContext.AddParameter("@Subtype", SqlDbType.NVarChar, filter.Subtype, parameters); if (!String.IsNullOrEmpty(filter.Name)) GitTimeContext.AddParameterForLike("@Name", filter.Name, parameters); if (!String.IsNullOrEmpty(filter.PersonName)) GitTimeContext.AddParameterForLike("@PersonName", filter.PersonName, parameters); return parameters; }