private void AddRuleToSelect(EntityModel em, SqlSelect select, SqlTable table, Condition rule)
{
var pm = em.Properties[rule.Property];
var col = new SqlColumn(pm.Name, table);
switch (rule.Operator)
{
case Condition.Is:
select.Where.Is(col, ConvertValue(pm, rule.Values.Single()));
break;
case Condition.GreaterThen:
select.Where.GreaterThen(col, GetValueForComparison(pm, rule.Values.Single()));
break;
case Condition.GreaterThenOrEqual:
select.Where.GreaterThenOrEqual(col, GetValueForComparison(pm, rule.Values.Single()));
break;
case Condition.LessThen:
select.Where.LessThen(col, GetValueForComparison(pm, rule.Values.Single()));
break;
case Condition.LessThenOrEqual:
select.Where.LessThenOrEqual(col, GetValueForComparison(pm, rule.Values.Single()));
break;
case Condition.StartsWith:
select.Where.StartsWith(col, rule.Values.Single() as string);
break;
case Condition.EndsWith:
select.Where.EndsWith(col, rule.Values.Single() as string);
break;
case Condition.ContainsPhrase:
select.Where.Like(col, rule.Values.Single() as string);
break;
case Condition.AnyOf:
select.Where.In(col, rule.Values);
break;
case Condition.Between:
select.Where.Between(col, rule.Values[0], rule.Values[1]);
break;
case Condition.Not:
select.Where.IsNot(col, rule.Values.Single());
break;
default:
throw new NotImplementedException(string.Format("Rule with operator {0} is not implemented by the EntityRepository.", rule.Operator));
}
}
public int Count(EntityQuery2 query)
{
var em = _domainService.Domain.Entities[query.Entity];
SqlTable main = new SqlTable(em.Name);
var select = new SqlSelect(main, new SqlColumn("ID", main));
select.Count = true;
if (query.AllProperties)
{
query.Properties = em.Properties.Select(p => p.Name).ToList();
}
if (query.RelatedTo.Count > 0)
{
select.Distinct = true;
}
select.AddColumns(query.Properties.Where(p => !p.Equals("id", StringComparison.InvariantCultureIgnoreCase)).Select(p => new SqlColumn(em.Properties[p].Name, main)).ToArray());
if (query.SortBy == null)
{
query.SortBy = new Sorting("ID");
}
if (!query.SortBy.IsRel)
{
var sortByColumn = new SqlColumn(query.SortBy.Property, main);
select.OrderBy = new SqlOrderBy(sortByColumn, query.SortBy.Descending);
}
int id = 1;
foreach (var rule in query.Rules)
{
AddRuleToSelect(em, select, main, rule);
}
foreach (var relto in query.RelatedTo)
{
if (relto.RelatedTo.Count > 0)
{
throw new NotImplementedException("Only one level of nested query (related) is allowed.");
}
var rel = em.GetRelation(relto.Entity, relto.Role);
var other = rel.GetOther(em.Name);
var relTable = new SqlTable(rel.Name, string.Format("tbl{0}", id++));
var otherTable = new SqlTable(other.Name, string.Format("tbl{0}", id++));
//TODO: code repetition with the includes logic!
if (rel.Left.Name == em.Name)
{
select.AddJoin(relTable, new SqlColumn("LID", relTable), new SqlColumn("ID", main));
select.AddJoin(otherTable, new SqlColumn("RID", relTable), new SqlColumn("ID", otherTable));
}
else
{
select.AddJoin(relTable, new SqlColumn("RID", relTable), new SqlColumn("ID", main));
select.AddJoin(otherTable, new SqlColumn("LID", relTable), new SqlColumn("ID", otherTable));
}
foreach (var rule in relto.Rules)
{
AddRuleToSelect(other, select, otherTable, rule);
}
foreach (var rule in relto.RelationRules)
{
AddRuleToSelect(rel, select, relTable, rule);
}
}
using (var ctx = _dbService.GetDatabaseContext(false))
{
SqlCommand cmd = select.ToSqlCommand(ctx.Connection);
int result = (int)cmd.ExecuteScalar();
return(result);
}
}
public IEnumerable <Entity> Search(EntityQuery2 query)
{
var em = _domainService.Domain.Entities[query.Entity];
SqlTable main = new SqlTable(em.Name);
var select = new SqlSelect(main, new SqlColumn("iD", main));
if (query.AllProperties)
{
query.Properties = em.Properties.Select(p => p.Name).ToList();
}
if (query.RelatedTo.Count > 0)
{
select.Distinct = true;
}
select.AddColumns(query.Properties.Where(p => !p.Equals("id", StringComparison.InvariantCultureIgnoreCase)).Select(p => new SqlColumn(em.Properties[p].Name, main)).ToArray());
if (query.SortBy == null)
{
query.SortBy = new Sorting("ID");
}
if (!query.SortBy.IsRel)
{
var sortByColumn = new SqlColumn(query.SortBy.Property, main);
select.OrderBy = new SqlOrderBy(sortByColumn, query.SortBy.Descending);
}
if (query.Paging != null)
{
var start = (query.Paging.Page - 1) * query.Paging.PageSize;
select.Paging = new SqlPaging(start + 1, start + query.Paging.PageSize);
}
int id = 1;
Dictionary <string, SqlSelect> additSearches = new Dictionary <string, SqlSelect>();
foreach (var inc in query.Includes)
{
var rel = em.GetRelation(inc.Entity, inc.Role);
var relType = rel.TypeFor(em.Name);
bool isLeft = rel.Left.Name == em.Name;
var other = isLeft ? rel.Right : rel.Left;
if (relType == RelationType.OneToOne || relType == RelationType.ManyToOne)
{
var relTable = new SqlTable(rel.Name, string.Format("tbl{0}", id++));
var entTable = new SqlTable(other.Name, string.Format("tbl{0}", id++));
if (isLeft)
{
select.AddJoin(relTable, new SqlColumn("LID", relTable), new SqlColumn("ID", main));
select.AddJoin(entTable, new SqlColumn("RID", relTable), new SqlColumn("ID", entTable));
}
else
{
select.AddJoin(relTable, new SqlColumn("RID", relTable), new SqlColumn("ID", main));
select.AddJoin(entTable, new SqlColumn("LID", relTable), new SqlColumn("ID", entTable));
}
select.AddColumns(rel.Properties.Select(p => new SqlColumn(p.Name, relTable)).ToArray());
select.AddColumns(other.Properties.Select(p => new SqlColumn(p.Name, entTable)).ToArray());
if (query.SortBy != null &&
query.SortBy.IsRel &&
query.SortBy.Role.Equals(rel.Role, StringComparison.InvariantCultureIgnoreCase) &&
query.SortBy.Entity.Equals(other.Name, StringComparison.InvariantCultureIgnoreCase))
{
//TODO: sort by rel properties
select.OrderBy = new SqlOrderBy(new SqlColumn(query.SortBy.Property, entTable), query.SortBy.Descending);
}
}
else
{
var relTbl = new SqlTable(rel.Name, rel.Name);
var s = new SqlSelect(relTbl, rel.Properties.Select(pm => new SqlColumn(pm.Name, relTbl)).ToArray());
var entTbl = new SqlTable(other.Name, other.Name);
s.AddJoin(entTbl, new SqlColumn(isLeft ? "rid" : "lid", relTbl), new SqlColumn("id", entTbl), SqlJoinType.Inner);
s.AddColumns(other.Properties.Select(pm => new SqlColumn(pm.Name, entTbl)).ToArray());
if (inc.SortBy == null)
{
inc.SortBy = new Sorting("ID");
}
var incSortCol = s.Columns.Find(c => c.Name.Equals(inc.SortBy.Property, StringComparison.InvariantCultureIgnoreCase));
if (incSortCol != null)
{
s.OrderBy = new SqlOrderBy(incSortCol, inc.SortBy.Descending);
}
if (inc.Paging != null)
{
var start = (inc.Paging.Page - 1) * inc.Paging.PageSize;
s.Paging = new SqlPaging(start + 1, start + inc.Paging.PageSize);
}
additSearches.Add(rel.Name, s);
}
}
//TODO: group?
foreach (var rule in query.Rules)
{
AddRuleToSelect(em, select, main, rule);
}
foreach (var relto in query.RelatedTo)
{
if (relto.RelatedTo.Count > 0)
{
throw new NotImplementedException("Only one level of nested query (related) is allowed.");
}
var rel = em.GetRelation(relto.Entity, relto.Role);
var other = rel.GetOther(em.Name);
var relTable = new SqlTable(rel.Name, string.Format("tbl{0}", id++));
var otherTable = new SqlTable(other.Name, string.Format("tbl{0}", id++));
//TODO: code repetition with the includes logic!
if (rel.Left.Name == em.Name)
{
select.AddJoin(relTable, new SqlColumn("LID", relTable), new SqlColumn("ID", main));
select.AddJoin(otherTable, new SqlColumn("RID", relTable), new SqlColumn("ID", otherTable));
}
else
{
select.AddJoin(relTable, new SqlColumn("RID", relTable), new SqlColumn("ID", main));
select.AddJoin(otherTable, new SqlColumn("LID", relTable), new SqlColumn("ID", otherTable));
}
foreach (var rule in relto.Rules)
{
AddRuleToSelect(other, select, otherTable, rule);
}
foreach (var rule in relto.RelationRules)
{
AddRuleToSelect(rel, select, relTable, rule);
}
}
using (var ctx = _dbService.GetDatabaseContext(false))
{
List <Entity> result = new List <Entity>();
SqlCommand cmd = select.ToSqlCommand(ctx.Connection);
using (var reader = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
var e = new Entity(em.Name, reader.GetInt32(0));
var idx = 1;
foreach (var prop in query.Properties)
{
var pm = em.Properties[prop];
if (pm.Is("id"))
{
continue;
}
e.Data[pm.Name] = GetValueFromReader(reader, pm, idx);
idx++;
}
foreach (var inc in query.Includes)
{
var rel = em.GetRelation(inc.Entity, inc.Role);
var relType = rel.TypeFor(em.Name);
bool isLeft = rel.Left.Name == em.Name;
var other = isLeft ? rel.Right : rel.Left;
if (relType == RelationType.OneToOne || relType == RelationType.ManyToOne)
{
var relData = new Relation(rel.Role, new Entity(other.Name));
foreach (var prop in rel.Properties)
{
relData.Data[prop.Name] = GetValueFromReader(reader, prop, idx++);
}
foreach (var prop in other.Properties)
{
relData.Entity.Data[prop.Name] = GetValueFromReader(reader, prop, idx++);
}
if (!relData.Entity.Data.ContainsKey("id") || relData.Entity.Data["id"] == null)
{
continue;
}
relData.Entity.Id = relData.Entity.GetData <int>("id");
e.SetSingleRelation(relData);
}
}
result.Add(e);
}
}
if (result.Count > 0)
{
foreach (var kvp in additSearches)
{
var rel = em.Relations[kvp.Key];
var sel = kvp.Value;
bool isLeft = em.Name == rel.Left.Name;
var other = isLeft ? rel.Right : rel.Left;
sel.Where.In(new SqlColumn(isLeft ? "lid" : "rid", sel.From), result.Select(e => e.Id));
SqlCommand getRelsCmd = sel.ToSqlCommand(ctx.Connection);
using (var reader = getRelsCmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
var idx = 0;
Relation relationData = new Relation(rel.Role, new Entity(other.Name));
foreach (var prop in rel.Properties)
{
relationData.Data[prop.Name] = GetValueFromReader(reader, prop, idx++);
}
foreach (var prop in other.Properties)
{
relationData.Entity.Data[prop.Name] = GetValueFromReader(reader, prop, idx++);
}
relationData.Entity.Id = relationData.Entity.GetData <int>("id");
var mainId = isLeft ? relationData.GetData <int>("lid") : relationData.GetData <int>("rid");
result.Find(e => e.Id == mainId).AddManyRelation(relationData);
}
}
}
}
ctx.Complete();
return(result);
}
}