我想使用查询中列表中的预加载查询数据.我需要查询返回为IQueryable,因为它用于网格&被分页(此处不包括).我需要从查找中加载标签以避免连接(实际代码中有更多内容).
我知道我可以执行ToList()并对其进行后处理,但是我需要IQueryable.这是代码：
// Run in intialization other code...
var contactLookups = new ContactsDbContext();
List<StatusType> _statusTypes = contactLookups.StatusTypes.ToList();
public IQueryable GetQuery()
{
var contactsCtx = new ContactsDbContext();
IQueryable query = contactsCtx.Select(contact => new
{
Id = contact.Id,
FullName = contact.FirstName + " " + contact.LastName,
CompanyName = contact.CompanyName,
Status = _statusTypes.FirstOrDefault(l => contact.StatusId == l.Id).Label
});
return query;
}
上面的代码引发错误,因为EF / LINQ无法将内存中的列表形成SQL(出于明显的原因)-除非添加/更改某些内容.
我想以某种方式告诉EF在SQL或类似的东西之后应用查找.我已经读过关于使用EF Helper代码和表达式执行类似操作的信息,但现在找不到该文章了.
注意,我对查询本身很灵活.唯一不可协商的是“ contact.StatusId”(int),但是像“列表”类型一样,其余结构“ _statusTypes.FirstOrDefault(l => contact.StatusId == l.Id)”已打开.
任何帮助表示赞赏.
解决方法:
您可以将EF的查询包装到您自己的IQueryable拦截实现中,在该实现中,您可以在将对象返回到应用程序之前注入内存中查找的值.
听起来可能很复杂,但实际上并不难实现.需要完成以下工作：
>将您实体中的Status属性标记为未映射(使用具有Fluent API的Ignore()或该属性上的[NotMapped]属性).
>写InterceptingQueryable< T> (因此命名)IOrderedQueryable< T>的实现,该实现包装了IQueryable< T> EF中的对象(在示例中由Select方法返回).
>编写InterceptingQueryProvider< T> IQueryProvider< T>的实现,其反过来包装从EF获得的查询提供程序.
>写InterceptingEnumerator< T> IEnumerator< T>的实现,其中继到由EF返回的枚举器对象.在执行MoveNext之后,此枚举器将立即注入Status属性的值(可以很容易地概括为以这种方式填充任何查找属性),以便完全填充Current返回的对象.
上面的链连接如下：
> InterceptingQueryable中继到EF的查询对象,在构造函数中传递.
> InterceptingQueryable.Provider属性返回InterceptingQueryProvider.
> InterceptingQueryable.GetEnumerator方法返回InterceptingEnumerator.
> InterceptingQueryProvider.CreateQuery方法从EF查询提供程序获取查询对象,然后将其包装在另一个InterceptingQueryable实例中返回.
> InterceptingQueryProvider.Execute在EF查询提供程序上调用Execute,然后在获取要进行查询注入的实体的情况下,以与InterceptingEnumerator相同的方式注入查询值(提取方法以供重用).
更新
这是代码：
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
namespace Examples.Queryables
{
public class InterceptingQueryable<T> : IOrderedQueryable<T>
{
private readonly Action<T> _interceptor;
private readonly IQueryable<T> _underlyingQuery;
private InterceptingQueryProvider _provider;
public InterceptingQueryable(Action<T> interceptor, IQueryable<T> underlyingQuery)
{
_interceptor = interceptor;
_underlyingQuery = underlyingQuery;
_provider = null;
}
public IEnumerator<T> GetEnumerator()
{
return new InterceptingEnumerator(_interceptor, _underlyingQuery.GetEnumerator());
}
IEnumerator IEnumerable.GetEnumerator()
{
return GetEnumerator();
}
public Expression Expression
{
get { return _underlyingQuery.Expression; }
}
public Type ElementType
{
get { return _underlyingQuery.ElementType; }
}
public IQueryProvider Provider
{
get
{
if ( _provider == null )
{
_provider = new InterceptingQueryProvider(_interceptor, _underlyingQuery.Provider);
}
return _provider;
}
}
private class InterceptingQueryProvider : IQueryProvider
{
private readonly Action<T> _interceptor;
private readonly IQueryProvider _underlyingQueryProvider;
public InterceptingQueryProvider(Action<T> interceptor, IQueryProvider underlyingQueryProvider)
{
_interceptor = interceptor;
_underlyingQueryProvider = underlyingQueryProvider;
}
public IQueryable CreateQuery(Expression expression)
{
throw new NotImplementedException();
}
public IQueryable<TElement> CreateQuery<TElement>(Expression expression)
{
var query = _underlyingQueryProvider.CreateQuery<TElement>(expression);
if ( typeof(T).IsAssignableFrom(typeof(TElement)) )
{
return new InterceptingQueryable<TElement>((Action<TElement>)(object)_interceptor, query);
}
else
{
return query;
}
}
public object Execute(Expression expression)
{
throw new NotImplementedException();
}
public TResult Execute<TResult>(Expression expression)
{
var result = _underlyingQueryProvider.Execute<TResult>(expression);
if ( result is T )
{
_interceptor((T)(object)result);
}
return result;
}
}
private class InterceptingEnumerator : IEnumerator<T>
{
private readonly Action<T> _interceptor;
private readonly IEnumerator<T> _underlyingEnumerator;
private bool _hasCurrent;
public InterceptingEnumerator(Action<T> interceptor, IEnumerator<T> underlyingEnumerator)
{
_interceptor = interceptor;
_underlyingEnumerator = underlyingEnumerator;
_hasCurrent = false;
}
public void Dispose()
{
_underlyingEnumerator.Dispose();
}
public bool MoveNext()
{
_hasCurrent = _underlyingEnumerator.MoveNext();
if ( _hasCurrent )
{
_interceptor(_underlyingEnumerator.Current);
}
return _hasCurrent;
}
public void Reset()
{
_underlyingEnumerator.Reset();
}
public T Current
{
get
{
return _underlyingEnumerator.Current;
}
}
object IEnumerator.Current
{
get { return Current; }
}
}
}
public static class QueryableExtensions
{
public static IOrderedQueryable<T> InterceptWith<T>(this IQueryable<T> query, Action<T> interceptor)
{
return new InterceptingQueryable<T>(interceptor, query);
}
}
}
这是测试用例/示例.首先,我们不要忘记将非映射的Status属性添加到Contact实体：
public partial class Contact
{
[NotMapped]
public StatusType Status { get; set; }
}
然后,我们可以使用拦截器机制,如下所示：
var contactLookups = contactsCtx.StatusTypes.ToList();
Action<Contact> interceptor = contact => {
contact.Status = contactLookups.FirstOrDefault(l => contact.StatusId == l.Id);
};
// note that we add InterceptWith(...) to entity set
var someContacts =
from c in contactsCtx.Contacts.InterceptWith(interceptor)
where c.FullName.StartsWith("Jo")
orderby c.FullName, c.CompanyName
select c;
Console.WriteLine("--- SOME CONTACTS ---");
foreach ( var c in someContacts )
{
Console.WriteLine(
"{0}: {1}, {2}, {3}={4}",
c.Id, c.FullName, c.CompanyName, c.StatusId, c.Status.Name);
}
打印：
--- SOME CONTACTS ---
1: John Smith, Acme Corp, 3=Status Three
3: Jon Snow, The Wall, 2=Status Two
然后查询被转换为：
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[FullName] AS [FullName],
[Extent1].[CompanyName] AS [CompanyName],
[Extent1].[StatusId] AS [StatusId]
FROM [dbo].[Contacts] AS [Extent1]
WHERE [Extent1].[FullName] LIKE 'Jo%'
ORDER BY [Extent1].[FullName] ASC, [Extent1].[CompanyName] ASC