DapperExtensions Generic Populate Model using Predicates from SeparateModels

Question

I am working on a project that utilizes Dapper, DapperExtensions using Generic Models and I am wondering how I can populate a model using the DapperExtension.GetAll method?

Below is the sql code that returns the records I am trying to filter on using DapperExtensions.

select f.*
from Item f
where f.CurrentStatus = 'Open'
AND f.ItemID not in (SELECT ItemID FROM ItemLog l WHERE f.ItemID = l.ItemID
AND l.Status != 'Escalated'
AND DateLogged <= DATEADD(mi, 25, GetDate())) //<- this value would be replaced with a variable

I did some research and found you can use the Split.on but am not sure if that would be appropriate in this situation or not

The GetAll Method looks like this so we do have the ability to filter on records

public virtual IEnumerable<TModel> GetAll(IList<DbFilter<TModel>> filters = null)
{
    filters = filters ?? new List<DbFilter<TModel>>();
    using (var db = Context)
    {
        var pg = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
        foreach (var filter in filters)
        {
             pg.Predicates.Add(Predicates.Field(filter.FilterExpression, filter.FilterOperator, filter.FilterItem));
        }
             return db.GetList<TModel>(pg).ToList();
        }
}

Any assistance would be greatly appreciated. I have entertained the idea of also creating a SPROC to populate the model. Just trying to determine the most efficient route.

Well I managed to populate my model using the following method, would still love to hear feedback or possible suggestions.

public async Task<IEnumerable<FormsFnol>> GetLateItems(DateTime responseTime)
{
     IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
     var items = await db.QueryAsync<FormsFnol>(@"
         SELECT f.*
         FROM Item f
         WHERE f.CurrentStatus = 'Open'
         AND f.ItemID not in (SELECT ItemID FROM ItemLog l WHERE f.ItemID = l.ItemID
         AND l.Status != 'Escalated'
         AND DateLogged <= @dateTime
     ", new { @dateTime = responseTime});

     return items;
 }

score 1 · Answer 1 · answered Sep 01 '17 at 16:00

Dapper Extensions does not support Stored Procedures, but Dapper does.

For your code, the SP would look something like this:

result = dbConnection.Query<FormsFnol>("FormsFnol_s", 
                                        new { dateTime = responseTime},
                                        null, 
                                        true, 
                                        null, 
                                        CommandType.StoredProcedure);

Your stored procedure would perform the select query that you have in the code. I would use a stored procedure for one very simple reason: If you need to change the selection methods, changing it in Sql is much easier than a change in the program itself.

I have been moving away from DapperExtensions myself because of the lack of documentation on how it is used, and the fact that it really hasn't been updated in a while.

score 0 · Accepted Answer · answered Aug 21 '17 at 22:14

Guess I'll use my answer since I haven't gotten any feedback positive or negative

public async Task<IEnumerable<FormsFnol>> GetLateItems(DateTime responseTime)
{
 IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
 var items = await db.QueryAsync<FormsFnol>(@"
     SELECT f.*
     FROM Item f
     WHERE f.CurrentStatus = 'Open'
     AND f.ItemID not in (SELECT ItemID FROM ItemLog l WHERE f.ItemID = l.ItemID
     AND l.Status != 'Escalated'
     AND DateLogged <= @dateTime
 ", new { @dateTime = responseTime});

 return items;
}

DapperExtensions Generic Populate Model using Predicates from SeparateModels

2 Answers2