c# - 使用 Sqlite.NET 的 Entity Framework 提示不支持应用连接

Question

我在使用带有 Entity Framework 的 Sqlite.NET 执行以下连接语句时遇到问题。

我的设置是:

.NET Framework v4 Entity Framework v6.0.0.0 SQLite.EF6 v1.0.99.0 SQLite.Linq v1.0.99.0

using (var me = new ChinookContext())
{
    var screenset =
        from track in me.Tracks
        join genre in me.Genres on track.GenreId equals genre.GenreId
        join artist in me.Artists on track.Album.ArtistId equals artist.ArtistId
        into artists
        from artist in artists.DefaultIfEmpty()
        select new TrackDTO
        {
            TrackId = track.TrackId,
            TrackName = track.Name,
            GenreId = genre.GenreId,
            GenreName = genre.Name,
            ArtistId = artist.ArtistId,
            ArtistName = artist.Name
        };
        foreach (var track in screenset)
        {
            Console.WriteLine(track.TrackId + ", " + track.TrackName + ", " + track.TrackPrice);
        }
}

我得到的错误是:

APPLY joins are not supported

这是我在不通过堆栈跟踪的情况下所能获得的最内在的内容。

数据模型基于开源的 Chinook 数据库。

有趣的是，我在其他 Sqlite.NET 查询中使用过连接语句，而且我以前没有遇到过这个问题。

感谢任何帮助。

提前致谢。

更新:

这是错误和堆栈跟踪:

An error occurred while preparing the command definition. See the inner exception for details.APPLY joins are not supported at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver, BridgeDataReaderFactory bridgeDataReaderFactory, ColumnMapFactory columnMapFactory) at System.Data.Entity.Core.EntityClient.Internal.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext) at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.CreateCommandDefinition(ObjectContext context, DbQueryCommandTree tree) at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Boolean streaming, Span span, IEnumerable1 compiledQueryParameters, AliasGenerator aliasGenerator)
at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable1 forMergeOption) at System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClass3.<GetResults>b__2()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClass3.<GetResults>b__1()
at System.Data.Entity.Core.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery1..GetEnumerator>b__0() at System.Lazy1.CreateValue()
at System.Lazy1.LazyInitValue() at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext() at SqlLiteChinook.Qxx.SearchMusic() in D:\Dev\Showcase\ServerSqlLite\Xxx\Qxx.cs:line 36 at SqlLiteChinook.Program.Main(String[] args) in D:\Dev\Showcase\ServerSqlLite\ProgramServer.cs:line 28

Answer 1

使用 Lambda 表达式，以下查询将有助于提供所需的结果。

由于表之间有良好的关系，您始终可以使用 .Include 方法，该方法会包含子表的所有行。

在下面的查询中，我在父表专辑和轨道之间进行了连接。专辑和轨道将分别包含它的子表艺术家和流派。

var query3 = db.Albums.Include("Artists").Join(db.Tracks.Include("Genre"), aa => aa.TrackId, tr => tr.ID, (aa, tr) => new
            {
                aa,
                tr
            }).Select(Finalrow => new TrackDTO
            {
                TrackId = Finalrow.tr.ID,
                TrackName = Finalrow.tr.Name,
                GenreId = Finalrow.tr.GENREs.Select(g => g.ID).FirstOrDefault(),
                GenreName = Finalrow.tr.GENREs.Select(g => g.Name).FirstOrDefault(),
                ArtistId = Finalrow.aa.Artists.Select(a => a.Id).FirstOrDefault(),
                ArtistName = Finalrow.aa.Artists.Select(a => a.Name).FirstOrDefault()

            }).OrderBy(t => t.TrackId);