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 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());
}
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 SimpleRowNumQuery()
{
Query query = new Query();
query.Select.Add(new SelectField("ID"));
query.Select.Add(new SelectField("Name"));
query.Select.Add(new SelectField(new RowNumberField().Order(new SortField("ID")), "Row"));
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], ROW_NUMBER() OVER (ORDER BY [ID] ASC) AS [Row] FROM [dbo].[Products] WHERE ([Name] = @name) ORDER BY [Name] ASC", sqlGenerator.GenerateSql());
}
public void Test1()
{
var query = new QueryBuilder()
.From(new Table("Products", "p1"))
.InnerJoin(new Table("p1"), new Table("Variants"), new DbField("Id", "p1"), new DbField("ProductId", "Variants"))
.Where(new DbField("Id", "p1"), ComparisonOperator.In, new ArrayField(1, 3, 6, 7, 8))
.Select(
new DbField("Name", "p1"),
new DbField("Id", "p1"),
new RowNumberField().Order(new DbField("Id", "p1"))
)
.Take(100)
.Build();
Assert.AreEqual("SELECT TOP 100 [p1].[Name], [p1].[Id], ROW_NUMBER() OVER (ORDER BY [p1].[Id] ASC) AS [RowNum] FROM [dbo].[Products] AS [p1] INNER JOIN [dbo].[Variants] ON [p1].[Id] = [Variants].[ProductId] WHERE ([p1].[Id] IN (1, 3, 6, 7, 8))", MsSqlGenerator.GenerateSql(query));
}