public void TwoLevelObjectWithBuilderTest()
{
Query query = new Query();
query.Select.Add(new SelectField("ID"));
query.Select.Add(new SelectField("Name"));
query.From = new Table("Products");
query.Where = new ConditionClause(new Condition {
Field1 = new DbField("Name"), Field2 = new Variable("name"), Operator = ComparisonOperator.Equal
});
query.OrderBy.Add(new SortField("Name"));
MsSqlGenerator sqlGenerator = new MsSqlGenerator();
sqlGenerator.DefaultTableSchema = null;
query.Accept(sqlGenerator);
Assert.AreEqual("SELECT [ID], [Name] FROM [Products] WHERE ([Name] = @name) ORDER BY [Name] ASC", sqlGenerator.GenerateSql());
SimpleObject[] result = conn.Query <SimpleObject>(sqlGenerator.GenerateSql(), new { name = "Test" }).ToArray();
Assert.AreEqual(1, result.Length);
Assert.AreEqual(1, result[0].ID);
conn.Close();
}
public static IEnumerable <TEntity> FindRecords <TEntity>(this DbSet <TEntity> dbSet, EntityQueryModel queryModel) where TEntity : class
{
var dbContext = dbSet.GetDbContext();
IModel model = dbContext.Model;
IEntityType entityType = model.FindEntityType(typeof(TEntity));
IEnumerable <IPropertyBase> properties = entityType.GetPropertiesAndNavigations();
string tableName = entityType.GetTableName();
string fullTableName = entityType.GetFullTableName();
List <SelectField> selectFields = new List <SelectField>();
List <TableJoin> joinedTables = new List <TableJoin>();
foreach (var field in properties)
{
switch (field)
{
case Property p:
selectFields.Add(new SelectField(p.GetColumnName(), p.Name));
break;
case Navigation n:
var navType = n.GetTargetType();
if (navType.GetFullTableName() == fullTableName)
{
var navProps = navType.GetProperties();
foreach (var navField in navProps)
{
selectFields.Add(new SelectField(navField.GetColumnName(), n.Name + "." + navField.Name));
}
}
else
{
}
break;
}
}
Query query = new Query();
query.Select = selectFields;
query.From = new Table(tableName, schema: entityType.GetSchema());
ISqlGenerator sqlGenerator = new MsSqlGenerator();
query.Accept(sqlGenerator);
string sql = sqlGenerator.GenerateSql();
return(dbContext.MapQuery <TEntity>(sql, null));
}
public void SimpleQuery()
{
Query query = new Query();
query.Select.Add(new SelectField("ID"));
query.Select.Add(new SelectField("Name"));
query.From = new Table("Products");
query.Where = new ConditionClause(new Condition {
Field1 = new DbField("Name"), Field2 = new Variable("name"), Operator = ComparisonOperator.Equal
});
query.OrderBy.Add(new SortField("Name"));
ISqlGenerator sqlGenerator = new MsSqlGenerator();
query.Accept(sqlGenerator);
Assert.AreEqual("SELECT [ID], [Name] FROM [dbo].[Products] WHERE ([Name] = @name) ORDER BY [Name] ASC", sqlGenerator.GenerateSql());
}
public void SimpleConditionsTest()
{
Query query = new Query();
query.Select.Add(new SelectField("ID"));
query.Select.Add(new SelectField("Name"));
query.From = new Table("Products");
query.Where = new ConditionClause(new Condition {
Field1 = new DbField("Name"), Field2 = new Variable("name"), Operator = ComparisonOperator.Equal
});
query.Where.OtherConditions.Add(new MoreCondition(LogicalOperator.And, new Condition(new DbField("ID"), ComparisonOperator.LessThan, new NumberField(5))));
query.OrderBy.Add(new SortField("Name", true));
ISqlGenerator sqlGenerator = new MsSqlGenerator();
query.Accept(sqlGenerator);
Assert.AreEqual("SELECT [ID], [Name] FROM [dbo].[Products] WHERE ([Name] = @name AND [ID] < 5) ORDER BY [Name] DESC", sqlGenerator.GenerateSql());
}
public void NestedConditionsTest()
{
Query query = new Query();
query.Select.Add(new SelectField("ID"));
query.Select.Add(new SelectField("Name"));
query.From = new Table("Products");
query.Where =
new ConditionClause(new DbField("Name"), ComparisonOperator.Equal, new Variable("name"))
.And(
new ConditionClause(new DbField("ID"), ComparisonOperator.LessThan, new NumberField(5))
.Or(new DbField("ID"), ComparisonOperator.GreaterThan, new NumberField(10))
);
query.OrderBy.Add(new SortField("Name", true));
ISqlGenerator sqlGenerator = new MsSqlGenerator();
query.Accept(sqlGenerator);
Assert.AreEqual("SELECT [ID], [Name] FROM [dbo].[Products] WHERE ([Name] = @name AND ([ID] < 5 OR [ID] > 10)) ORDER BY [Name] DESC", sqlGenerator.GenerateSql());
}
