IQueryable. where()不能创建正确的SQL
本文关键字：创建 SQL 不能 where IQueryable | 更新日期: 2023-09-27 18:04:22
我有这个简单的代码:
public class AirportRepository : Repository<Airport>
{
public IQueryable<Airport> GetByCountry(Entity country)
{
IQueryable<Airport> airports = GetAll().Where( a => a.CountryId.Equals(country.Id) );
return airports;
}
}
…我认为将创建以下SQL:
SELECT * FROM Airport WHERE CountryID = ?
…但是它创建了以下SQL:
SELECT * FROM Airport
…然后在客户端执行Where()部分，这在我的例子中是非常昂贵的。
怎么回事?
UPDATE:下面是base Repository类的样子:
public abstract class Repository<T> : IRepository<T> where T : Entity, new()
{
protected SimpleSQLManager SQLManager = DatabaseManager.Instance.SQLManager;
public IQueryable<T> GetAll()
{
IQueryable<T> all = SQLManager.Table<T>().AsQueryable();
return all;
}
}
UPDATE #2:我不能分享SQLManager背后的代码，但是在深入研究它的代码之后，我可以看到Table是一个IEnumerable。这是问题所在吗?
UPDATE #3:这是相关的(我希望)SQLManager代码:
/// <summary>
/// Returns a queryable interface to the table represented by the given type.
/// </summary>
/// <returns>
/// A queryable object that is able to translate Where, OrderBy, and Take
/// queries into native SQL.
/// </returns>
public TableQuery<T> Table<T>() where T : new()
{
Initialize(false);
return _db.Table<T>();
}
public class TableQuery<T> : IEnumerable<T> where T : new()
{
public SQLiteConnection Connection { get; private set; }
public TableMapping Table { get; private set; }
Expression _where;
List<Ordering> _orderBys;
int? _limit;
int? _offset;
class Ordering
{
public string ColumnName { get; set; }
public bool Ascending { get; set; }
}
TableQuery (SQLiteConnection conn, TableMapping table)
{
Connection = conn;
Table = table;
}
public TableQuery (SQLiteConnection conn)
{
Connection = conn;
Table = Connection.GetMapping (typeof(T));
}
public TableQuery<T> Clone ()
{
var q = new TableQuery<T> (Connection, Table);
q._where = _where;
if (_orderBys != null) {
q._orderBys = new List<Ordering> (_orderBys);
}
q._limit = _limit;
q._offset = _offset;
return q;
}
public TableQuery<T> Where (Expression<Func<T, bool>> predExpr)
{
if (predExpr.NodeType == ExpressionType.Lambda) {
var lambda = (LambdaExpression)predExpr;
var pred = lambda.Body;
var q = Clone ();
q.AddWhere (pred);
return q;
} else {
throw new NotSupportedException ("Must be a predicate");
}
}
public TableQuery<T> Take (int n)
{
var q = Clone ();
q._limit = n;
return q;
}
public TableQuery<T> Skip (int n)
{
var q = Clone ();
q._offset = n;
return q;
}
public TableQuery<T> OrderBy<U> (Expression<Func<T, U>> orderExpr)
{
return AddOrderBy<U> (orderExpr, true);
}
public TableQuery<T> OrderByDescending<U> (Expression<Func<T, U>> orderExpr)
{
return AddOrderBy<U> (orderExpr, false);
}
protected TableQuery<T> AddOrderBy<U>(Expression<Func<T, U>> orderExpr, bool asc)
{
if (orderExpr.NodeType == ExpressionType.Lambda) {
var lambda = (LambdaExpression)orderExpr;
var mem = lambda.Body as MemberExpression;
if (mem != null && (mem.Expression.NodeType == ExpressionType.Parameter)) {
var q = Clone ();
if (q._orderBys == null) {
q._orderBys = new List<Ordering> ();
}
q._orderBys.Add (new Ordering {
ColumnName = mem.Member.Name,
Ascending = asc
});
return q;
} else {
throw new NotSupportedException ("Order By does not support: " + orderExpr);
}
} else {
throw new NotSupportedException ("Must be a predicate");
}
}
protected void AddWhere(Expression pred)
{
if (_where == null) {
_where = pred;
} else {
_where = Expression.AndAlso (_where, pred);
}
}
protected SQLiteCommand GenerateCommand(string selectionList)
{
var cmdText = "select " + selectionList + " from '"" + Table.TableName + "'"";
var args = new List<object> ();
if (_where != null) {
var w = CompileExpr (_where, args);
cmdText += " where " + w.CommandText;
}
if ((_orderBys != null) && (_orderBys.Count > 0)) {
var t = string.Join (", ", _orderBys.Select (o => "'"" + o.ColumnName + "'"" + (o.Ascending ? "" : " desc")).ToArray ());
cmdText += " order by " + t;
}
if (_limit.HasValue) {
cmdText += " limit " + _limit.Value;
}
if (_offset.HasValue) {
if (!_limit.HasValue) {
cmdText += " limit -1 ";
}
cmdText += " offset " + _offset.Value;
}
return Connection.CreateCommand (cmdText, args.ToArray ());
}
protected class CompileResult
{
public string CommandText { get; set; }
public object Value { get; set; }
}
protected CompileResult CompileExpr(Expression expr, List<object> queryArgs)
{
if (expr == null) {
throw new NotSupportedException ("Expression is NULL");
} else if (expr is BinaryExpression) {
var bin = (BinaryExpression)expr;
var leftr = CompileExpr (bin.Left, queryArgs);
var rightr = CompileExpr (bin.Right, queryArgs);
//If either side is a parameter and is null, then handle the other side specially (for "is null"/"is not null")
string text;
if (leftr.CommandText == "?" && leftr.Value == null)
text = CompileNullBinaryExpression(bin, rightr);
else if (rightr.CommandText == "?" && rightr.Value == null)
text = CompileNullBinaryExpression(bin, leftr);
else
text = "(" + leftr.CommandText + " " + GetSqlName(bin) + " " + rightr.CommandText + ")";
return new CompileResult { CommandText = text };
} else if (expr.NodeType == ExpressionType.Call) {
var call = (MethodCallExpression)expr;
var args = new CompileResult[call.Arguments.Count];
for (var i = 0; i < args.Length; i++) {
args [i] = CompileExpr (call.Arguments [i], queryArgs);
}
var sqlCall = "";
if (call.Method.Name == "Like" && args.Length == 2) {
sqlCall = "(" + args [0].CommandText + " like " + args [1].CommandText + ")";
} else if (call.Method.Name == "Contains" && args.Length == 2) {
sqlCall = "(" + args [1].CommandText + " in " + args [0].CommandText + ")";
} else {
sqlCall = call.Method.Name.ToLower () + "(" + string.Join (",", args.Select (a => a.CommandText).ToArray ()) + ")";
}
return new CompileResult { CommandText = sqlCall };
} else if (expr.NodeType == ExpressionType.Constant) {
var c = (ConstantExpression)expr;
queryArgs.Add (c.Value);
return new CompileResult {
CommandText = "?",
Value = c.Value
};
} else if (expr.NodeType == ExpressionType.Convert) {
var u = (UnaryExpression)expr;
var ty = u.Type;
var valr = CompileExpr (u.Operand, queryArgs);
return new CompileResult {
CommandText = valr.CommandText,
Value = valr.Value != null ? Convert.ChangeType (valr.Value, ty) : null
};
} else if (expr.NodeType == ExpressionType.MemberAccess) {
var mem = (MemberExpression)expr;
if (mem.Expression.NodeType == ExpressionType.Parameter) {
//
// This is a column of our table, output just the column name
//
return new CompileResult { CommandText = "'"" + mem.Member.Name + "'"" };
} else {
object obj = null;
if (mem.Expression != null) {
var r = CompileExpr (mem.Expression, queryArgs);
if (r.Value == null) {
throw new NotSupportedException ("Member access failed to compile expression");
}
if (r.CommandText == "?") {
queryArgs.RemoveAt (queryArgs.Count - 1);
}
obj = r.Value;
}
//
// Get the member value
//
object val = null;
if (mem.Member.MemberType == MemberTypes.Property) {
var m = (PropertyInfo)mem.Member;
val = m.GetValue (obj, null);
} else if (mem.Member.MemberType == MemberTypes.Field) {
var m = (FieldInfo)mem.Member;
val = m.GetValue (obj);
} else {
throw new NotSupportedException ("MemberExpr: " + mem.Member.MemberType.ToString ());
}
//
// Work special magic for enumerables
//
if (val != null && val is System.Collections.IEnumerable && !(val is string)) {
var sb = new System.Text.StringBuilder();
sb.Append("(");
var head = "";
foreach (var a in (System.Collections.IEnumerable)val) {
queryArgs.Add(a);
sb.Append(head);
sb.Append("?");
head = ",";
}
sb.Append(")");
return new CompileResult {
CommandText = sb.ToString(),
Value = val
};
}
else {
queryArgs.Add (val);
return new CompileResult {
CommandText = "?",
Value = val
};
}
}
}
throw new NotSupportedException ("Cannot compile: " + expr.NodeType.ToString ());
}
/// <summary>
/// Compiles a BinaryExpression where one of the parameters is null.
/// </summary>
/// <param name="parameter">The non-null parameter</param>
protected string CompileNullBinaryExpression(BinaryExpression expression, CompileResult parameter)
{
if (expression.NodeType == ExpressionType.Equal)
return "(" + parameter.CommandText + " is ?)";
else if (expression.NodeType == ExpressionType.NotEqual)
return "(" + parameter.CommandText + " is not ?)";
else
throw new NotSupportedException("Cannot compile Null-BinaryExpression with type " + expression.NodeType.ToString());
}
string GetSqlName (Expression expr)
{
var n = expr.NodeType;
if (n == ExpressionType.GreaterThan)
return ">"; else if (n == ExpressionType.GreaterThanOrEqual) {
return ">=";
} else if (n == ExpressionType.LessThan) {
return "<";
} else if (n == ExpressionType.LessThanOrEqual) {
return "<=";
} else if (n == ExpressionType.And) {
return "and";
} else if (n == ExpressionType.AndAlso) {
return "and";
} else if (n == ExpressionType.Or) {
return "or";
} else if (n == ExpressionType.OrElse) {
return "or";
} else if (n == ExpressionType.Equal) {
return "=";
} else if (n == ExpressionType.NotEqual) {
return "!=";
} else {
throw new System.NotSupportedException ("Cannot get SQL for: " + n.ToString ());
}
}
public int Count ()
{
return GenerateCommand("count(*)").ExecuteScalar<int> ();
}
public IEnumerator<T> GetEnumerator ()
{
return GenerateCommand ("*").ExecuteQuery<T> ().GetEnumerator ();
}
System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator ()
{
return GetEnumerator ();
}
}
您问为什么它正在创建"错误的"SQL。让我们一步一步来。
LINQ非常强大，但有时会误导开发人员。
.Where(a => a.CountryId.Equals(country.Id))是按国家过滤机场的有效和工作语法(我可以建议重写a.CountryId == country.id吗?更好的可读性)。
现在你看不到LINQ后台发生了什么，因为它完全取决于你调用Where的实际对象。正如其他人指出的那样，在你的GetAll()中调用AsQueryable是原因。
GetAll()执行SQL查询，不进行过滤，因此它将返回所有Airport，然后在内存中过滤结果集。
更糟糕的是，您的SQLManager似乎是ORM的粗略实现。您必须与您的软件架构师讨论如何实现Entity Framework或NHibernate(但我不再推荐第二个)，它们是支持LINQ的合适orm。使用它们，您将发现查询将被正确过滤。
我的解决方案是创建另一个GetAll()方法，该方法接受谓词，然后将该方法用于不同的GetBySomething()方法;
public abstract class Repository<T> : IRepository<T> where T : Entity, new()
{
protected SimpleSQLManager SQLManager = DatabaseManager.Instance.SQLManager;
public IQueryable<T> GetAll()
{
IQueryable<T> all = SQLManager.Table<T>().AsQueryable();
return all;
}
public IQueryable<T> GetAll(Expression<Func<T, bool>> predicate)
{
IQueryable<T> all = SQLManager.Table<T>().Where(predicate).AsQueryable();
return all;
}
}
public class AirportRepository : Repository<Airport>
{
public IQueryable<Airport> GetByCountry(Entity country)
{
IQueryable<Airport> airports = GetAll( a => a.CountryId == country.Id );
return airports;
}
}
这个工作，但我认为它不是很漂亮?