private static void ShowCase_Query_Simple(ExampleDbContext dbContext)
{
// Simple Select //
var persons = dbContext.Persons.Select().Build().Execute();
var students = dbContext.Students
.Select()
.Build()
.Execute();
var classes = dbContext.Classes
.Select()
.Build()
.Execute();
var teachers = dbContext.Teachers
.Select()
.Build()
.Execute();
// Select with Where //
var filteredPerson = dbContext.Persons
.Select()
.Where(BinaryExpression.Eq(new ColumnExpression("firstname"), new ValueExpression("test_firstname")))
.Build()
.Execute();
var allPersons = dbContext.Persons
.Select()
.Where(BinaryExpression.Eq(new ValueExpression(1), new ValueExpression(1)))
.Build()
.Execute();
}
private static void Updates(SampleDbContext ctx)
{
// custom queries are somewhat supported
var course = ctx.Courses.Where(new WhereSqlFragment(
BinaryExpression.Eq(
//Course with 'Name' equal to 'SWE3'
new Accessor {
PropertyName = "Name", TableName = "Course"
},
Value.String("SWE3", "name")
)
)).First();
// changes to tracked objects are automatically persisted once saveChanges is called
course.Teacher.Salary += 1_000;
course.Active = false;
ctx.SaveChanges();
var students = ctx.Students.All();
// remove all inactive courses from students
// remove operation on manyToMany relation
foreach (var student in students)
{
student.Courses = student.Courses
.Where(c => c.Active)
.ToList();
}
ctx.SaveChanges();
}
public void Setup()
{
TestEntity = new Entity("table_name", new List<Column>(), typeof(TestEntity), null, new List<System.Type>(), 1);
var col1 = new Column("column1", null, null, null, new OrmDbType("text", PreparedStatementTypeMapper.Map(typeof(string))), false);
col1.Entity = TestEntity;
var col2 = new Column("column2", null, null, null, new OrmDbType("text", PreparedStatementTypeMapper.Map(typeof(string))), false);
col2.Entity = TestEntity;
TestColumns = new List<Column>() { col1, col2 };
UpdateStatements = new List<UpdateColumnStatement>() {
new UpdateColumnStatement(
"column1",
new ValueExpression("othervalue", PreparedStatementTypeMapper.Map(typeof(string)), "a")
),
new UpdateColumnStatement(
"column2",
new ValueExpression("newValue", PreparedStatementTypeMapper.Map(typeof(string)), "b")
)
};
WhereFilters = new List<IWhereFilter>
{
BinaryExpression.Eq(
new ColumnExpression("column2"),
new ValueExpression("test", PreparedStatementTypeMapper.Map(typeof(string)), "test2")
)
};
}
public DeleteQuery(DbContext ctx, Entity entity, T obj)
{
_ctx = ctx;
Entity = entity;
var pk = Entity.PrimaryKeyAttribute.InvokeGetter(obj);
_where = new WhereSqlFragment(
BinaryExpression.Eq(
new Accessor {
TableName = Entity.TableName, PropertyName = Entity.PrimaryKeyAttribute.DataBaseColumn
},
Value.FromAttr(pk, Entity.PrimaryKeyAttribute)
));
}
public UpdateStatement(DbContext context, Entity entity, TEntity obj, IEnumerable <IValueChange> changes)
{
_object = obj;
_context = context;
Entity = entity;
_changes = changes;
_returningAttributes = Entity.Attributes.Values.Where(attr => !attr.IsEntityType);
_whereClause = new WhereSqlFragment(BinaryExpression.Eq(
new Accessor {
TableName = Entity.TableName, PropertyName = Entity.PrimaryKeyAttribute.DataBaseColumn
},
Value.FromAttr(
Entity.PrimaryKeyAttribute.InvokeGetter(_object),
Entity.PrimaryKeyAttribute
)));
}
private ICollection <T> LoadOneToMany <T>(object poco, Column column, ref ICollection <T> loadTo, OneToMany relation)
{
var targetEntity = relation.MappedByEntity;
var selectQueryBuilder = new SelectQueryBuilder <T>(_ctx, relation.MappedByPocoType);
var whereColumn = targetEntity.Columns.Where(c => c.PropInfo == relation.MappedByProperty).First();
// primary key of the current object!
var whereValue = _entity.PkColumn.PropInfo.GetMethod.Invoke(poco, new object[0]);
selectQueryBuilder = selectQueryBuilder
.Where(
BinaryExpression.Eq(
new ColumnExpression(whereColumn.Name),
new ValueExpression(whereValue, whereColumn.DbType.PStmtDbType)
)
);
return(selectQueryBuilder.Build().Execute().ToList());
}
public void TestSelectMultipleWhere()
{
var where = BinaryExpression.GT(
new ColumnExpression("column1"),
new ValueExpression(5, PreparedStatementTypeMapper.Map(typeof(int)), "test")
);
var where2 = BinaryExpression.Eq(
new ColumnExpression("column2"),
new ValueExpression("hallo", PreparedStatementTypeMapper.Map(typeof(string)), "test2")
);
var selectQuery = new SelectQuery <TestEntity>(
null,
TestEntity,
TestColumns,
new List <IWhereFilter>()
{
where, where2
},
new List <Join>());
Assert.AreEqual("SELECT t1.column1, t1.column2 FROM table_name t1 WHERE t1.column1 > @test AND t1.column2 = @test2;", selectQuery.AsSqlString());
}
private T LoadManyToOne <T>(object poco, Column column, ref T loadTo, ManyToOne relation)
{
var cache = _ctx.Cache;
var targetEntity = relation.ToEntity;
var selectQueryBuilder = new SelectQueryBuilder <T>(_ctx, relation.ToPocoType);
var pk = _entity.PkColumn.PropInfo.GetMethod.Invoke(poco, new object[0]);
// load shadowEntity from cache because it holds the foreign key
var cachePoco = cache.GetOrInsert(_entity, (long)pk, poco);
cachePoco.ShadowAttributes.TryGetValue(column.Name, out var fk);
var whereColumn = relation.To;
selectQueryBuilder = selectQueryBuilder
.Where(
BinaryExpression.Eq(
new ColumnExpression(whereColumn.Name),
new ValueExpression(fk, whereColumn.DbType.PStmtDbType)
)
);
return(selectQueryBuilder.Build().Execute().FirstOrDefault());
}
private IWhereFilter BuildWhere(string columnName, object value)
{
return(BinaryExpression.Eq(
new ColumnExpression(columnName),
new ValueExpression(value)));
}