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()); }