编译的LINQ联接 [英] Compiled LINQ joins

问题描述

我的项目时间有些闲散，所以我决定出演微型优化剧院.我正在使用的应用程序将在某些性能很低的平板电脑上运行，因此我一直在寻找加快处理速度的方法.鉴于我使用的是LINQ to Entities，因此我研究了预编译查询以提高性能，因此想出了一个简单的查询来返回给定公司的联系人列表

I have got a bit of slack project time so I decided to star in a production of micro optimisation theatre. The app I’m working on is going to be run on some quite low performance tablets so I was looking for ways to speed things up. Given that I’m using LINQ to Entities I looked into precompiled queries to boast performance and so came up with this simple one to return a list of contacts for a given company

 Public ReadOnly pContacts_list_query As Func(Of SpiraxDDWEntities, Integer, IQueryable(Of tblContacts)) = _
        CompiledQuery.Compile(Of SpiraxDDWEntities, Integer, IQueryable(Of tblContacts))(Function(ctx As SpiraxDDWEntities, pCompany_ABN As Integer) _
                                                                                           From Contact_data In ctx.tblContacts Where Contact_data.AccountNumber = pCompany_ABN
                                                                                           )

现在就可以了，因为它只有一个表，所以IQueryable类型可以是表名.我的问题是，如果我想使用联接预编译查询该怎么办?例如这个

Now that’s fine as its just one table so the IQueryable type can be the table name. My question is what if I wanted to precompile a query with joins? For example this one

    Dim Quote_QRY = From Quote_data In linEntities.tblQuote
                    Join Quote_value_data In linEntities.tblQuoteValue On Quote_data.ID Equals Quote_value_data.QuoteID
                    Join Quote_status_data In linEntities.tblQuoteStatus On Quote_data.Status Equals Quote_status_data.Abbreviation
                    Where Quote_data.AccountNo = Me.txtCompany_ABN.Text
                    Select Quote_data.ID, Status = Quote_status_data.Description, Quote_data.Contact, Quote_data.Project, Quote_value_data.QuoteValue

我该怎么办?

谢谢

推荐答案

这里的联接不是问题.该问题正转变为匿名类型.在这种情况下，您需要创建一个命名类并在select子句中将其投影到其中:

The join here isn't the issue. The issue is projecting into an anonymous type. In this case, you need to create a named class and project into it in your select clause:

Public Class Quote
   Public Property ID As String
   Public Property Status As String
   Public Property Contact As String
   Public Property Project As String
   Public Property QuoteValue As String
End Class

 Public ReadOnly Quote_query As Func(Of SpiraxDDWEntities, Integer, IQueryable(Of Quotes)) = _ 
        CompiledQuery.Compile(Of SpiraxDDWEntities, Integer, IQueryable(Of Quotes))(Function(ctx As SpiraxDDWEntities, pCompany_ABN As Integer) 
                    From Quote_data In linEntities.tblQuote 
                    Join Quote_value_data In linEntities.tblQuoteValue On Quote_data.ID Equals Quote_value_data.QuoteID 
                    Join Quote_status_data In linEntities.tblQuoteStatus On Quote_data.Status Equals Quote_status_data.Abbreviation 
                    Where Quote_data.AccountNo = Me.txtCompany_ABN.Text 
                    Select New Quote With {
                       .ID = Quote_data.ID, 
                       .Status = Quote_status_data.Description, 
                       .Contact = Quote_data.Contact, 
                       .Project = Quote_data.Project, 
                       .QuoteValue = Quote_value_data.QuoteValue 
                   }

另一个警告:如果编译查询要求生成的类型是否为映射实体类型，我不确定.如果是这样，您可能需要更改EDMX，以使EF认为该类型有效.或者，您可能需要考虑在CSDL中使用定义查询.

One additional caveat: I'm not sure off the top of my head if Compiled Query requires that the resulting type be a mapped entity type or not. If so, you may need to alter the EDMX to make EF think that the type is valid. Alternatively, you may want to consider using a Defining query in the CSDL.

这篇关于编译的LINQ联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！