Skip to content

Instantly share code, notes, and snippets.

@cristipufu
Last active April 8, 2018 15:54
Show Gist options
  • Save cristipufu/cd5e58bba0c06c0ab85fd9257986a54f to your computer and use it in GitHub Desktop.
Save cristipufu/cd5e58bba0c06c0ab85fd9257986a54f to your computer and use it in GitHub Desktop.
SqlQuery<T> EFCore
public static class DatabaseExtensions
{
public static SqlQuery<T> SqlQuery<T>(this DatabaseFacade database, string sqlQuery, object parameters = null)
where T : class
{
return new SqlQuery<T>
{
Database = database,
Query = sqlQuery,
Parameters = GetParameters(parameters)
};
}
public static SqlQuery SqlQuery(this DatabaseFacade database, string sqlQuery, object parameters = null)
{
return new SqlQuery
{
Database = database,
Query = sqlQuery,
Parameters = GetParameters(parameters)
};
}
private static SqlParameter[] GetParameters(object parameters = null)
{
IDictionary<string, object> dictionary;
if (parameters is IDictionary<string, object> objects)
{
dictionary = objects;
}
else
{
dictionary = new Dictionary<string, object>();
if (parameters != null)
{
foreach (var property in parameters.GetType().GetProperties())
{
dictionary[property.Name] = property.GetValue(parameters);
}
}
}
return dictionary.Select(x => SqlParametersNullSafe.Create(
x.Key.StartsWith("@") ? x.Key : $"@{x.Key}",
x.Value))
.ToArray();
}
}
public class SqlQuery<T>
{
public DatabaseFacade Database { get; set; }
public string Query { get; set; }
public SqlParameter[] Parameters { get; set; }
private readonly IMapper _mapper;
public SqlQuery() : this(createMapping: true)
{
}
public SqlQuery(bool createMapping)
{
if (createMapping)
{
_mapper = new MapperConfiguration(m =>
{
m.AddDataReaderMapping();
m.CreateMap<IDataRecord, T>();
}).CreateMapper();
}
}
public Task<T> FirstOrDefaultAsync()
{
return FirstOrDefaultAsync(CancellationToken.None);
}
public async Task<T> FirstOrDefaultAsync(CancellationToken cancellationToken)
{
return (await ToListAsync(cancellationToken)).FirstOrDefault();
}
public Task<IList<T>> ToListAsync()
{
return ToListAsync(CancellationToken.None);
}
public async Task<IList<T>> ToListAsync(CancellationToken cancellationToken)
{
var connection = Database.GetDbConnection();
var transaction = Database.CurrentTransaction.GetDbTransaction();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = Query;
cmd.Transaction = transaction;
if (Parameters.Any())
{
cmd.Parameters.AddRange(Parameters.ToArray());
}
var shouldCloseConnection = false;
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
shouldCloseConnection = true;
}
try
{
using (var reader = await cmd.ExecuteReaderAsync(cancellationToken))
{
return Read(reader);
}
}
finally
{
if (shouldCloseConnection)
{
cmd.Connection.Close();
}
}
}
}
protected virtual IList<T> Read(IDataReader reader)
{
return _mapper.Map<IDataReader, IEnumerable<T>>(reader).ToList();
}
}
public class SqlQuery : SqlQuery<dynamic>
{
public SqlQuery() : base(createMapping: false)
{
}
protected override IList<dynamic> Read(IDataReader reader)
{
var results = new List<dynamic>();
while (reader.Read())
{
results.Add(GetDataRow(reader));
}
return results;
}
private static dynamic GetDataRow(IDataRecord reader)
{
var dataRow = new ExpandoObject() as IDictionary<string, object>;
for (var fieldCount = 0; fieldCount < reader.FieldCount; fieldCount++)
{
dataRow.Add(reader.GetName(fieldCount), reader[fieldCount]);
}
return dataRow;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment