/// <summary>
/// Construct which takes the underlying list and its associated context.
/// </summary>
/// <param name="list"></param>
/// <param name="context"></param>
public ModelList(DefaultScope.List list, ListSampleOfflineContext context)
{
this.list = list;
this.context = context;
items = null;
// Register for the property changed event
list.PropertyChanged += new PropertyChangedEventHandler(list_PropertyChanged);
}
public virtual async Task <IEnumerable <T> > GetTopNAsync(Expression <Func <T, bool> > filter = null, Func <IQueryable <T>, IOrderedQueryable <T> > orderBy = null, string includeProperties = "", int noOfItems = 0)
{
// extract the dynamic sql query and parameters from predicate
if (filter != null)
{
QueryResult result = DynamicQuery.GetTopNDynamicQuery(_tableName, filter, noOfItems);
return(await WithConnection(async c => {
var results = await c.QueryAsync <T>(result.Sql, (object)result.Param);
return results;
}));
}
else
{
return(await WithConnection(async c => {
var results = await c.QueryAsync <T>("SELECT * FROM " + _tableName);
return results;
}));
}
}
public async Task <T> FindByEmailAsync(string email)
{
ThrowIfDisposed();
if (email == null)
{
throw new ArgumentNullException(nameof(email));
}
var q = new DynamicQuery <T>
(
$"select top 1 * from c where c.email = @email {EntityTypeConstraint}",
new
{
email
}
);
return(await _repo.GetFirstOrDefaultAsync(q.ToSqlQuerySpec()));
}
/// <summary>
/// Finds the specified predicate.
/// </summary>
/// <param name="predicate">The predicate.</param>
/// <returns>A list of users that match the query.</returns>
public override IEnumerable <Skill> Find(Expression <Func <Skill, bool> > predicate)
{
List <Skill> items = new List <Skill>();
// extract the dynamic sql query and parameters from predicate
QueryResult result = DynamicQuery.GetDynamicQuery("Skill", predicate);
using (IDbConnection cn = Connection)
{
cn.Open();
var results = cn.Query(result.Sql, (object)result.Param);
for (int i = 0; i < results.Count(); i++)
{
items.Add(MapSkill(results.ElementAt(i)));
}
}
return(items);
}
public async Task <Project> GetProjectAsync(string instance, string id)
{
if (instance == null)
{
throw new ArgumentNullException(nameof(instance));
}
if (string.IsNullOrEmpty(id))
{
return(null);
}
var query = new DynamicQuery("id eq @id", new { id });
var complex = new ComplexQuery(null, query.ToSqlQuery(), 1, null, null, instance);
var fin = _translator.Translate(complex);
var result = await GetFirstOrDefaultAsync(new DynamicQuery(fin)).ConfigureAwait(false);
return(result);
}
public virtual async Task <IPagination <TEntity> > FindAllAsync(int pageIndex, int pageSize, string fieldOrderBy, bool desc = false, Expression <Func <TEntity, bool> > filter = null)
{
QueryResult filterSql = null;
IDictionary <string, Object> expando = new ExpandoObject();
if (filter == null)
{
filterSql = new QueryResult(this.selectDefault, expando);
}
else
{
filterSql = DynamicQuery.GetDynamicQuery <TEntity>(this.tableName, this.fieldIdName.First().Key, filter);
}
string sql = filterSql.Sql;
expando = filterSql.Param;
return(await FindAllAsync(pageIndex, pageSize, fieldOrderBy, sql, desc, expando));
}
public async Task <T> FindByNameAsync(string userName)
{
ThrowIfDisposed();
if (userName == null)
{
throw new ArgumentNullException(nameof(userName));
}
var q = new DynamicQuery <T>
(
$"select top 1 * from c where c.userName = @userName {EntityTypeConstraint}",
new
{
userName
}
);
return(await _repo.GetFirstOrDefaultAsync(q.ToSqlQuerySpec()));
}
public async Task SelectEscapedAsync()
{
var q = new DynamicQuery("select * from c where c.entity = @entity and c.message = @message",
new
{
entity = Robot.Entity,
message = @"\'\\''"
}
);
var robot = await _repo.GetFirstOrDefaultAsync(q).ConfigureAwait(false);
Assert.AreNotEqual(robot, null);
var oldId = robot.Id;
robot = await _repo.GetFirstOrDefaultAsync(oldId).ConfigureAwait(false);
Assert.AreNotEqual(robot, null);
Assert.AreEqual(robot.Id, oldId);
}
public void SelectEscaped()
{
var q = new DynamicQuery("select * from c where c.entity = @entity and c.message = @message",
new
{
entity = Robot.Entity,
message = @"\'\\''"
}
);
var robot = _repo.GetFirstOrDefault(q);
Assert.AreNotEqual(robot, null);
var oldId = robot.Id;
robot = _repo.GetFirstOrDefault(oldId);
Assert.AreNotEqual(robot, null);
Assert.AreEqual(robot.Id, oldId);
}
//reference from http://www.bradoncode.com/blog/2012/12/creating-data-repository-using-dapper.html
public static T Insert <T>(this IDbConnection cnn, string tableName, string primaryIdName, dynamic param, bool tableWithTrigger = false, bool withPrimaryId = false)
{
string query = DynamicQuery.GetInsertQuery(tableName, primaryIdName, param, tableWithTrigger, withPrimaryId);
IEnumerable <T> result = SqlMapper.Query <T>(cnn, query, param);
if (result.Count() > 0)
{
return(result.First());
}
else if (withPrimaryId)
{
PropertyInfo[] props = param.GetType().GetProperties();
object data = props.FirstOrDefault(s => s.Name == primaryIdName);
return((T)((PropertyInfo)data).GetValue(param));
}
else
{
object data = Guid.Parse(query.Split('\'')[1]);
return((T)data);
}
}
public void QueryDynamicParams()
{
using (var cn = LocalDb.GetConnection(dbName))
{
var qry = new DynamicQuery("[SomeDate]>@minDate")
{
FirstName = "peabody"
};
var results = qry.Execute(cn, setParams: (queryParams) =>
{
queryParams.Add("minDate", new DateTime(1990, 1, 1));
});
Assert.IsTrue(qry.ResolvedSql.Equals("SELECT [FirstName], [Weight], [SomeDate], [Notes], [Id] FROM [SampleTable] WHERE [SomeDate]>@minDate AND [FirstName] LIKE '%'+@firstName+'%' ORDER BY [FirstName]"));
Assert.IsTrue(qry.DebugSql.ReplaceWhitespace().Equals(
@"DECLARE @FirstName nvarchar(max), @minDate datetime;
SET @FirstName = 'peabody';
SET @minDate = '1/1/1990 12:00:00 AM';
SELECT [FirstName], [Weight], [SomeDate], [Notes], [Id] FROM [SampleTable] WHERE [SomeDate]>@minDate AND [FirstName] LIKE '%'+@firstName+'%' ORDER BY [FirstName]".ReplaceWhitespace()));
}
}
public void Generate_Postgre_Query_With_Filled_Params_And_Search_Test()
{
// Arrange
string query = @"Select * From Person Where Name={{data.name}}";
DynamicQueryBuilder postgreDynamicQueryBuilder = new DynamicQueryBuilder();
// Act
DynamicQuery dynamicQuery = postgreDynamicQueryBuilder
.Init(
query,
new List <LetPortal.Portal.Models.DynamicLists.FilledParameter>
{
new LetPortal.Portal.Models.DynamicLists.FilledParameter
{
Name = "data.name",
Value = "aaa"
}
})
.Build();
// Assert
Assert.NotEmpty(dynamicQuery.Parameters);
}
/// <summary>
/// 翻译ExistsSQL
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="selectSql"></param>
/// <param name="parms"></param>
/// <param name="exp"></param>
public virtual SqlBuilder GetExistsSQLByLambda <T>(Expression <Func <T, bool> > exp) where T : class, new()
{
StringBuilder selectSql = new StringBuilder();
List <DbParameter> parms = new List <DbParameter>();
EntityInfo entityInfo = Caches.EntityInfoCache.Get(typeof(T));
selectSql.AppendFormat("SELECT COUNT(1) CT FROM {0}", entityInfo.TableName);
if (exp != null)
{
QueryResult result = DynamicQuery.GetDynamicWhere(exp);
selectSql.AppendFormat(" WHERE {0}", result.Sql);
foreach (var parm in result.Param)
{
parms.Add(GetDbParameter(parm.Key, parm.Value));
}
}
return(new SqlBuilder
{
Sql = selectSql.ToString(),
DbParameters = parms
});
}
public IList <T> ObtenerTodos()
{
IList <T> retorno = new List <T>();
try
{
Conexion.Abrir();
DynamicQuery consulta = Conexion.CrearQueryDinamica();
foreach (IDictionary <string, object> registro in consulta.Consultar())
{
T entidad = Mapear(registro);
retorno.Add(entidad);
}
}
finally
{
Conexion.Cerrar();
}
return(retorno);
}
public static DynamicQuery Converter(DynamicQuery dynamicQuery)
{
if (dynamicQuery == null)
{
return(null);
}
var result = new DynamicQuery(dynamicQuery.Pager);
result.Take = dynamicQuery.Take;
result.Skip = dynamicQuery.Skip;
result.ParamGroup = new DynamicQueryParamGroup();
result.Order = ConverterOrderFieldName(dynamicQuery.Order);
if (dynamicQuery.ParamGroup != null)
{
var newgroup = new DynamicQueryParamGroup();
ConverterDynamicQueryParamGroupFieldName(newgroup, dynamicQuery.ParamGroup);
result.ParamGroup = newgroup;
}
result.Select = ConverterSelectFieldName(dynamicQuery.Select);
return(result);
}
public async Task <TRole> FindByNameAsync(string normalizedRoleName, CancellationToken cancellationToken)
{
cancellationToken.ThrowIfCancellationRequested();
ThrowIfDisposed();
if (normalizedRoleName == null)
{
throw new ArgumentNullException(nameof(normalizedRoleName));
}
var dynamicQuery = new DynamicQuery
(
$"select top 1 * from c where c.normalizedName = @normalizedRoleName {EntityTypeConstraint}",
new
{
normalizedRoleName
}
);
var role = await _repository.GetFirstOrDefaultAsync(dynamicQuery).ConfigureAwait(false);
return(role);
}
async Task <bool> DeleteDocumentVersionsAsync(Entities.Document document)
{
var q = new DynamicQuery
(
"select c.id as Id from c where c.entity = @entity and c.instance = @instance " +
"and c.projectId = @projectId and c.documentId = @documentId",
new
{
entity = Entities.DocumentVersion.Entity,
instance = document.Instance,
projectId = document.ProjectId,
documentId = document.Identifier
}
);
var items = await _repoDynamic.GetListAsync(q).ConfigureAwait(false);
foreach (var item in items)
{
await _repoDynamic.DeleteAsync(item.Id).ConfigureAwait(false);
}
return(true);
}
/// <summary>
/// Obtiene las entidades según los valores de un filtro.
/// </summary>
/// <remarks>
/// Registro de versiones:
///
/// 1.0 02/08/2016 Marcos Abraham Hernández Bravo (Ada Ltda.): versión inicial.
/// </remarks>
/// <param name="filtro">Mensaje de filtro</param>
/// <returns>Colección de mensajes que cumplen el filtro.</returns>
public IList <MensajeTO> ObtenerSegunFiltro(MensajeTO filtro)
{
IList <MensajeTO> retorno = new List <MensajeTO>();
try
{
Conexion.Abrir();
DynamicQuery consulta = Conexion.CrearQueryDinamica();
AgregarParametros(filtro, consulta);
foreach (IDictionary <string, object> registro in consulta.Consultar())
{
MensajeTO entidad = Mapear(registro);
retorno.Add(entidad);
}
}
finally
{
Conexion.Cerrar();
}
return(retorno);
}
/// <summary>
/// Gets the list of documents.
/// </summary>
/// <returns>The documents.</returns>
/// <param name="query">Query.</param>
public async Task <IList <T> > GetListAsync(DynamicQuery query)
{
return(await _repository.GetListHelperAsync(query).ConfigureAwait(false));
}
internal async Task <Entities.Document> FindDocumentByIdAsync(string instance, Uri projectId, Uri documentId, long?version)
{
// get actual version
if (version == null)
{
var q = new DynamicQuery
(
"select * from c where c.entity = @entity and c.instance = @instance and c.projectId = @projectId and c.identifier = @identifier",
new
{
entity = Entities.Document.Entity,
instance,
projectId = projectId.ToString(),
identifier = documentId.ToString()
}
);
var document = await _repoDocument.GetFirstOrDefaultAsync(q).ConfigureAwait(false);
return(document);
}
// get document from snapshot
var q2 = new DynamicQuery
(
"select * from c where c.entity = @entity and c.instance = @instance and c.projectId = @projectId and c.documentId = @identifier and c.version = @version",
new
{
entity = Entities.DocumentVersion.Entity,
instance,
projectId = projectId.ToString(),
identifier = documentId.ToString(),
version
}
);
var x = q2.ToSqlQuerySpec().ToSqlQuery();
var documentVersion = await _repoDocumentVersion.GetFirstOrDefaultAsync(q2).ConfigureAwait(false);
if (documentVersion == null)
{
return(null);
}
var newDocument = new Entities.Document
(
documentVersion.Instance,
documentVersion.ProjectId,
documentVersion.DocumentId,
documentVersion.DocumentTypeId,
documentVersion.Hash,
documentVersion.Document.Slug,
documentVersion.Document.OriginalMeta,
documentVersion.Document.Meta,
documentVersion.Document.Content,
documentVersion.Document.Date == null ? (DateTime?)null : documentVersion.Document.Date.DateTime,
documentVersion.Document.Draft,
documentVersion.Version
);
return(newDocument);
}
public void TestTransformation()
{
var dq = new DynamicQuery
{
Columns = new List <Column>
{
new Column {
Name = "p_id", Alias = "person_id"
},
new Column {
Name = "*", Alias = "value"
}
},
Transformations = new List <Transformation>
{
new Transformation {
IfNull = "value", ConstantValue = "."
},
new Transformation {
Input = "person_id, value", Format = "{0}_{1}", Alias = "personId_value"
},
new Transformation {
Input = "{value}, value", Format = "{0}_{1}", Alias = "FieldName_value"
},
new Transformation {
Input = "{#TableName}, {value}, value", Format = "{0}_{1}_{2}", Alias = "TableName_FieldName_value"
},
new Transformation {
Input = "{#TableName}, {value}, value", Format = "{0}_{1}_{2}", Alias = "MapKey1", Lookup = new TestLookup()
},
new Transformation {
Input = "{#TableName}, {value}, value", Format = "{0}_{1}", Alias = "MapKey2", Lookup = new TestLookup()
},
new Transformation {
IfNull = "MapKey1", Input = "MapKey2", Alias = "MapKey"
}
}
};
using (var reader = GetData())
{
while (reader.Read())
{
var resultTable = new DataTable();
resultTable.Load(dq.GetResults("test_table", reader));
Assert.AreEqual(resultTable.Columns.Count, 8);
Assert.AreEqual(resultTable.Columns[0].ColumnName, "person_id");
Assert.AreEqual(resultTable.Columns[1].ColumnName, "value");
Assert.AreEqual(resultTable.Columns[2].ColumnName, "personId_value");
Assert.AreEqual(resultTable.Columns[3].ColumnName, "FieldName_value");
Assert.AreEqual(resultTable.Columns[4].ColumnName, "TableName_FieldName_value");
Assert.AreEqual(resultTable.Columns[5].ColumnName, "MapKey1");
Assert.AreEqual(resultTable.Columns[6].ColumnName, "MapKey2");
Assert.AreEqual(resultTable.Columns[7].ColumnName, "MapKey");
Assert.AreEqual(resultTable.Rows.Count, 3);
Assert.AreEqual(resultTable.Rows[0]["person_id"], 1);
Assert.AreEqual(resultTable.Rows[1]["person_id"], 1);
Assert.AreEqual(resultTable.Rows[2]["person_id"], 1);
Assert.AreEqual(resultTable.Rows[0]["value"], 100);
Assert.AreEqual(resultTable.Rows[1]["value"], ".");
Assert.AreEqual(resultTable.Rows[2]["value"], 300);
Assert.AreEqual(resultTable.Rows[0]["personId_value"], "1_100");
Assert.AreEqual(resultTable.Rows[1]["personId_value"], "1_.");
Assert.AreEqual(resultTable.Rows[2]["personId_value"], "1_300");
Assert.AreEqual(resultTable.Rows[0]["FieldName_value"], "value1_100");
Assert.AreEqual(resultTable.Rows[1]["FieldName_value"], "value2_.");
Assert.AreEqual(resultTable.Rows[2]["FieldName_value"], "value3_300");
Assert.AreEqual(resultTable.Rows[0]["TableName_FieldName_value"], "test_table_value1_100");
Assert.AreEqual(resultTable.Rows[1]["TableName_FieldName_value"], "test_table_value2_.");
Assert.AreEqual(resultTable.Rows[2]["TableName_FieldName_value"], "test_table_value3_300");
Assert.AreEqual(resultTable.Rows[0]["MapKey1"], "key1");
Assert.IsTrue(resultTable.Rows[1]["MapKey1"] is DBNull);
Assert.AreEqual(resultTable.Rows[2]["MapKey1"], "key3");
Assert.IsTrue(resultTable.Rows[0]["MapKey2"] is DBNull);
Assert.AreEqual(resultTable.Rows[1]["MapKey2"], "key2");
Assert.IsTrue(resultTable.Rows[2]["MapKey2"] is DBNull);
Assert.AreEqual(resultTable.Rows[0]["MapKey"], "key1");
Assert.AreEqual(resultTable.Rows[1]["MapKey"], "key2");
Assert.AreEqual(resultTable.Rows[2]["MapKey"], "key3");
}
}
}
public static async Task InsertAsync <T>(this IDbConnection cnn, string tableName, string primaryIdName, dynamic param)
{
await SqlMapper.Query <T>(cnn, DynamicQuery.GetInsertQuery(tableName, primaryIdName, param), param);
}
public static void Update(this IDbConnection cnn, string tableName, string primaryIdName, dynamic param)
{
SqlMapper.Execute(cnn, DynamicQuery.GetUpdateQuery(tableName, primaryIdName, param), param);
}
private ContextModel()
{
UserID = Guid.Empty;
userTags = null;
LoadUserID();
}
/// <summary>
/// Gets the first or default document.
/// </summary>
/// <returns>The first document.</returns>
/// <param name="query">Query.</param>
public T GetFirstOrDefault(DynamicQuery query)
{
return(AsyncTools.RunSync(() => GetFirstOrDefaultAsync(query)));
}
/// <summary>
/// Gets the first or default document.
/// </summary>
/// <returns>The first document.</returns>
/// <param name="query">Query.</param>
public async Task <T> GetFirstOrDefaultAsync(DynamicQuery query)
{
return(await _repository.GetFirstOrDefaultHelperAsync(query).ConfigureAwait(false));
}
public Table(string connectionString, string table)
{
_dynamicQuery = new DynamicQuery(connectionString, table);
}
async Task <Entities.DocumentType> GetFirstOrDefaultDocumentTypeAsync(DynamicQuery q)
{
var documentType = await _repoDocumentType.GetFirstOrDefaultAsync(q).ConfigureAwait(false);
return(documentType);
}
/// <summary>
/// Takes the tag id and the number of items associated with the tag
/// </summary>
/// <param name="tagId"></param>
/// <param name="count"></param>
public ModelTag(int tagId, int count)
{
this.tagId = tagId;
this.count = count;
items = null;
}
public DynamicQuery()
{
_QueryBuilder = this;
}
public static void Insert <T>(this IDbConnection cnn, string tableName, dynamic param, IDbTransaction dbTransaction = null)
{
SqlMapper.Query <T>(cnn, DynamicQuery.GetInsertQuery(tableName, param), param, dbTransaction);
}
/// <summary>
/// 默认构造方法。
/// </summary>
public BaseController()
{
this.DynamicQuery = Container.Resolve<DynamicQuery>();
}
public static void Update(this IDbConnection cnn, string tableName, dynamic param, IDbTransaction dbTransaction = null)
{
SqlMapper.Execute(cnn, DynamicQuery.GetUpdateQuery(tableName, param), param, dbTransaction);
}
async Task <IList <Entities.DocumentVersion> > GetDocumentVersionsAsync(DynamicQuery q)
{
var documentVersions = await _repoDocumentVersion.GetListAsync(q).ConfigureAwait(false);
return(documentVersions);
}
/// <summary>
/// Gets the list of documents.
/// </summary>
/// <returns>The documents.</returns>
/// <param name="query">Query.</param>
public IList <T> GetList(DynamicQuery query)
{
return(AsyncTools.RunSync(() => GetListAsync(query)));
}
