public override List <T> LoadWhere <T>(Entity entity, ICompiled query, Parameter[] parameters, int page = 0, int pageSize = 0, bool ascending = true, params Property[] orderBy)
{
Transaction trans = Transaction.RunningTransaction;
QueryExecutionContext context = query.GetExecutionContext();
foreach (Parameter queryParameter in parameters)
{
if (queryParameter.Value is null)
{
context.SetParameter(queryParameter.Name, null);
}
else
{
context.SetParameter(queryParameter.Name, entity.Parent.PersistenceProvider.ConvertToStoredType(queryParameter.Value.GetType(), queryParameter.Value));
}
}
StringBuilder sb = new StringBuilder();
sb.Append(context.CompiledQuery.QueryText);
if (orderBy != null && orderBy.Length != 0)
{
Property odd = orderBy.FirstOrDefault(item => !entity.IsSelfOrSubclassOf(item.Parent));
if (odd != null)
{
throw new InvalidOperationException(string.Format("Order property '{0}' belongs to the entity '{1}' while the query only contains entities of type '{2}'.", odd.Name, odd.Parent.Name, entity.Name));
}
sb.Append(" ORDER BY ");
sb.Append(string.Join(", ", orderBy.Select(item => string.Concat("node.", item.Name))));
if (ascending == false)
{
sb.Append(" DESC ");
}
}
if (pageSize > 0)
{
sb.Append(" SKIP ");
sb.Append(page * pageSize);
sb.Append(" LIMIT ");
sb.Append(pageSize);
}
Dictionary <string, object?>?customState = null;
var args = entity.RaiseOnNodeLoading(trans, null, sb.ToString(), context.QueryParameters, ref customState);
var result = trans.Run(args.Cypher, args.Parameters);
return(Load <T>(entity, args, result, trans));
}
public void OGMImplPlannerHitsUsing()
{
using (ConsoleOutput output = new ConsoleOutput())
{
using (Transaction.Begin(true))
{
Person p1 = new Person
{
Name = "Martin Sheen",
};
Person p2 = new Person
{
Name = "Michael Douglas",
};
Person p3 = new Person
{
Name = "Oliver Stone",
};
Person p4 = new Person
{
Name = "Rob Reiner",
};
Movie wallstreet = new Movie
{
Title = "Wall Street"
};
Movie tap = new Movie
{
Title = "The American President"
};
p1.ActedInMovies.Add(tap);
p1.ActedInMovies.Add(wallstreet);
p2.ActedInMovies.Add(tap);
p2.ActedInMovies.Add(wallstreet);
p3.DirectedMovies.Add(wallstreet);
p4.DirectedMovies.Add(tap);
Transaction.Commit();
}
}
using (ConsoleOutput output = new ConsoleOutput())
{
string consoleOutput;
using (Transaction.Begin())
{
// Force to use index
ICompiled compiled = Transaction.CompiledQuery
.Match(node.Movie.Alias(out MovieAlias m))
.UsingIndex(m.Title)
.Where(m.Title == "Wall Street")
.Return(m.Title)
.Compile();
var result = compiled.GetExecutionContext().Execute();
var a = result[0] as IDictionary <string, object>;
Assert.AreEqual(a["Column1"], "Wall Street");
consoleOutput = output.GetOuput();
Assert.IsTrue(Regex.IsMatch(consoleOutput, @"(MATCH \(n0:Movie\))[^a-zA-Z,0-9]*(USING INDEX n0:Movie\(Title\))[^a-zA-Z,0-9]*(WHERE \(n0.Title = ""Wall Street""\))[^a-zA-Z,0-9]*(RETURN DISTINCT n0.Title AS Column1)"));
// With relationship
compiled = Transaction.CompiledQuery
.Match(node.Movie.Alias(out MovieAlias ma).Out.PERSON_DIRECTED.In.Person.Alias(out PersonAlias p))
.UsingIndex(ma.Title)
.Where(ma.Title == "Wall Street")
.Return(ma.Title, p.Name)
.Compile();
result = compiled.GetExecutionContext().Execute();
a = result[0] as IDictionary <string, object>;
Assert.AreEqual(a["Column1"], "Wall Street");
Assert.AreEqual(a["Column2"], "Oliver Stone");
consoleOutput = output.GetOuput();
Assert.IsTrue(Regex.IsMatch(consoleOutput, @"(MATCH \(n0:Movie\)<-\[:DIRECTED_BY\]-\(n1:Person\))[^a-zA-Z,0-9]*(USING INDEX n0:Movie\(Title\))[^a-zA-Z,0-9]*(WHERE \(n0.Title = ""Wall Street""\))[^a-zA-Z,0-9]*(RETURN DISTINCT n0.Title AS Column1, n1.Name AS Column2)"));
// Use label scan
compiled = Transaction.CompiledQuery
.Match(node.Movie.Alias(out MovieAlias mas))
.UsingScan(mas)
.Where(mas.Title == "Wall Street")
.Return(mas.Title)
.Compile();
result = compiled.GetExecutionContext().Execute();
a = result[0] as IDictionary <string, object>;
Assert.AreEqual(a["Column1"], "Wall Street");
consoleOutput = output.GetOuput();
Assert.IsTrue(Regex.IsMatch(consoleOutput, @"(MATCH \(n0:Movie\))[^a-zA-Z,0-9]*(USING SCAN n0:Movie)[^a-zA-Z,0-9]*(WHERE \(n0.Title = ""Wall Street""\))[^a-zA-Z,0-9]*(RETURN DISTINCT n0.Title AS Column1)"));
// use label scan with relationship
compiled = Transaction.CompiledQuery
.Match(node.Movie.Alias(out MovieAlias mar).Out.PERSON_DIRECTED.In.Person.Alias(out PersonAlias par))
.UsingScan(mar)
.UsingScan(par)
.Where(mar.Title == "Wall Street")
.Return(mar.Title, par.Name)
.Compile();
result = compiled.GetExecutionContext().Execute();
a = result[0] as IDictionary <string, object>;
Assert.AreEqual(a["Column1"], "Wall Street");
Assert.AreEqual(a["Column2"], "Oliver Stone");
consoleOutput = output.GetOuput();
Assert.IsTrue(Regex.IsMatch(consoleOutput, @"(MATCH \(n0:Movie\)<-\[:DIRECTED_BY\]-\(n1:Person\))[^a-zA-Z,0-9]*(USING SCAN n0:Movie)[^a-zA-Z,0-9]*(USING SCAN n1:Person)[^a-zA-Z,0-9]*(WHERE \(n0.Title = ""Wall Street""\))[^a-zA-Z,0-9]*(RETURN DISTINCT n0.Title AS Column1, n1.Name AS Column2)"));
// use label scan and index
compiled = Transaction.CompiledQuery
.Match(node.Movie.Alias(out MovieAlias msi).Out.PERSON_DIRECTED.In.Person.Alias(out PersonAlias psi))
.UsingIndex(msi.Title)
.UsingScan(psi)
.Where(msi.Title == "Wall Street")
.Return(msi.Title, psi.Name)
.Compile();
result = compiled.GetExecutionContext().Execute();
a = result[0] as IDictionary <string, object>;
Assert.AreEqual(a["Column1"], "Wall Street");
Assert.AreEqual(a["Column2"], "Oliver Stone");
consoleOutput = output.GetOuput();
Assert.IsTrue(Regex.IsMatch(consoleOutput, @"(MATCH \(n0:Movie\)<-\[:DIRECTED_BY\]-\(n1:Person\))[^a-zA-Z,0-9]*(USING INDEX n0:Movie\(Title\))[^a-zA-Z,0-9]*(USING SCAN n1:Person)[^a-zA-Z,0-9]*(WHERE \(n0.Title = ""Wall Street""\))[^a-zA-Z,0-9]*(RETURN DISTINCT n0.Title AS Column1, n1.Name AS Column2)"));
}
}
}
public void OGMImplQueryOptionalMatch()
{
using (ConsoleOutput output = new ConsoleOutput())
{
using (Transaction.Begin(true))
{
Person p1 = new Person
{
Name = "Martin Sheen",
};
Person p2 = new Person
{
Name = "Michael Douglas",
};
Person p3 = new Person
{
Name = "Oliver Stone",
};
Person p4 = new Person
{
Name = "Rob Reiner",
};
Movie wallstreet = new Movie
{
Title = "Wall Street"
};
Movie tap = new Movie
{
Title = "The American President"
};
p1.ActedInMovies.Add(tap);
p1.ActedInMovies.Add(wallstreet);
p2.ActedInMovies.Add(tap);
p2.ActedInMovies.Add(wallstreet);
p3.DirectedMovies.Add(wallstreet);
p4.DirectedMovies.Add(tap);
Transaction.Commit();
}
}
using (ConsoleOutput output = new ConsoleOutput())
{
string outputConsole;
using (Transaction.Begin())
{
ICompiled compiled = Transaction.CompiledQuery
.Match(node.Person.Alias(out PersonAlias p))
.Where(p.Name.Contains("Martin Sheen"))
.OptionalMatch(node.Movie.Alias(out MovieAlias m))
.Return(m.Title)
.OrderBy(m.Title)
.Compile();
var result = compiled.GetExecutionContext().Execute();
var a = result[0] as IDictionary <string, object>;
var b = result[1] as IDictionary <string, object>;
Assert.AreEqual(a["Column1"], "The American President");
Assert.AreEqual(b["Column1"], "Wall Street");
outputConsole = output.GetOuput();
Assert.IsTrue(Regex.IsMatch(outputConsole, @"(MATCH \(n0:Person\))[^a-zA-Z,0-9]*(WHERE \(n0\.Name CONTAINS ""Martin Sheen""\))[^a-zA-Z,0-9]*(OPTIONAL MATCH \(n1:Movie\))[^a-zA-Z,0-9]*(RETURN DISTINCT n1\.Title AS Column1)[^a-zA-Z,0-9]*(ORDER BY n1\.Title)"));
compiled = Transaction.CompiledQuery
.Match(node.Person.Alias(out PersonAlias pa))
.Where(pa.Name.Contains("Martin Sheen"))
.OptionalMatch(pa.In.PERSON_DIRECTED.Out.Movie.Alias(out MovieAlias ma))
.Return(pa.Name, ma.Title)
.OrderBy(ma.Title)
.Compile();
result = compiled.GetExecutionContext().Execute();
a = result[0] as IDictionary <string, object>;
Assert.AreEqual(a["Column1"], "Martin Sheen");
Assert.IsNull(a["Column2"]);
outputConsole = output.GetOuput();
Assert.IsTrue(Regex.IsMatch(outputConsole, @"(MATCH \(n0:Person\))[^a-zA-Z,0-9]*(WHERE \(n0\.Name CONTAINS ""Martin Sheen""\))[^a-zA-Z,0-9]*(OPTIONAL MATCH \(n0\)-\[\:DIRECTED_BY\]\-\>\(n1:Movie\))[^a-zA-Z,0-9]*(RETURN DISTINCT n0\.Name AS Column1, n1\.Title AS Column2)[^a-zA-Z,0-9]*(ORDER BY n1\.Title)"));
compiled = Transaction.CompiledQuery
.Match(node.Person.Alias(out PersonAlias pap).In.PERSON_DIRECTED.Out.Movie.Alias(out MovieAlias mam))
.Where(pap.Name.Contains("Martin Sheen"))
.Return(mam.Title)
.OrderBy(mam.Title)
.Compile();
result = compiled.GetExecutionContext().Execute();
Assert.Zero(result.Count);
outputConsole = output.GetOuput();
Assert.IsTrue(Regex.IsMatch(outputConsole, @"(MATCH \(n0:Person\)-\[\:DIRECTED_BY\]\-\>\(n1:Movie\))[^a-zA-Z,0-9]*(WHERE \(n0\.Name CONTAINS ""Martin Sheen""\))[^a-zA-Z,0-9]*(RETURN DISTINCT n1\.Title AS Column1)[^a-zA-Z,0-9]*(ORDER BY n1\.Title)"));
}
}
}