VB Example Not Caching with Caching Policy

Jan 22, 2016 at 3:16 AM
The caching works great with the default InMemoryCache. Lovely, really.

My goal is to exclude just one table and allow all the rest to be cached. However, when I attempt to use a Caching Policy (below), nothing gets cached.

I need to do this in VB, not my first choice, but required. I may be missing some little VB thing.

Thanks!
Public Class Configuration
    Inherits System.Data.Entity.DbConfiguration
    Public Sub New()
        Dim transactionHandler = New CacheTransactionHandler(New InMemoryCache())
        AddInterceptor(transactionHandler)
        Dim CachingPolicy = New CachingPolicy
        AddHandler Loaded, Sub(sender As Object, e As DbConfigurationLoadedEventArgs) e.ReplaceService(Of DbProviderServices)(
            Function(serviceInterceptor As DbProviderServices, o As Object) New CachingProviderServices(serviceInterceptor, transactionHandler, New MyPolicy())
            )

    End Sub
End Class


Public Class MyPolicy
    Inherits CachingPolicy
    Protected Overrides Function CanBeCached(affectedEntitySets As ReadOnlyCollection(Of EntitySetBase), sql As String, parameters As IEnumerable(Of KeyValuePair(Of String, Object))) As Boolean
        Dim names = affectedEntitySets.[Select](Function(e) e.Table)
        Dim hashIncoming = New HashSet(Of String)(names)

        Dim cacheable = New List(Of HashSet(Of String))({
                New HashSet(Of String)({"IPLocation"}),
                New HashSet(Of String)({"NewsletterSignup"}),
                New HashSet(Of String)({"PageControl"}),
                New HashSet(Of String)({"ControlMarkup"}),
                New HashSet(Of String)({"DoSIP"}),
                New HashSet(Of String)({"PageVersion"}),
                New HashSet(Of String)({"Page"}),
                New HashSet(Of String)({"Airport"}),
                New HashSet(Of String)({"UserRole"}),
                New HashSet(Of String)({"User"}),
                New HashSet(Of String)({"ControlFlightSearch"}),
                New HashSet(Of String)({"CultureLibrary"}),
                New HashSet(Of String)({"Airline"}),
                New HashSet(Of String)({"SiteIPAccess"}),
                New HashSet(Of String)({"ThemeLayout"}),
                New HashSet(Of String)({"CSS"}),
                New HashSet(Of String)({"ControlNewsletterSignup"}),
                New HashSet(Of String)({"Country"}),
                New HashSet(Of String)({"IPLocationCountry"}),
                New HashSet(Of String)({"ELMAH_Error"}),
                New HashSet(Of String)({"AirportCulture"}),
                New HashSet(Of String)({"StateProvince"}),
                New HashSet(Of String)({"SiteClone"}),
                New HashSet(Of String)({"CountryCulture"}),
                New HashSet(Of String)({"ControlFlightQuote"}),
                New HashSet(Of String)({"ControlFlightModify"}),
                New HashSet(Of String)({"ControlFlightFilter"}),
                New HashSet(Of String)({"Role"}),
                New HashSet(Of String)({"StateProvinceCulture"}),
                New HashSet(Of String)({"ControlConfirm"}),
                New HashSet(Of String)({"ControlCart"}),
                New HashSet(Of String)({"SiteURL"}),
                New HashSet(Of String)({"SitePath"}),
                New HashSet(Of String)({"Site"}),
                New HashSet(Of String)({"ControlCarSearch"}),
                New HashSet(Of String)({"ControlExitPopup"}),
                New HashSet(Of String)({"Control"}),
                New HashSet(Of String)({"SiteCulture"}),
                New HashSet(Of String)({"ControlFlightSearchResponsive"}),
                New HashSet(Of String)({"ControlMarkupCulture"}),
                New HashSet(Of String)({"ControlStatusOfReservation"}),
                New HashSet(Of String)({"ControlFlightMatrix"}),
                New HashSet(Of String)({"ControlHotDeal"}),
                New HashSet(Of String)({"UITheme"}),
                New HashSet(Of String)({"CMPPhone"}),
                New HashSet(Of String)({"ControlCustomerSupport"}),
                New HashSet(Of String)({"ControlCarFilter"}),
                New HashSet(Of String)({"ControlCarQuote"}),
                New HashSet(Of String)({"ControlLogin"}),
                New HashSet(Of String)({"Culture"}),
                New HashSet(Of String)({"PageContainer"}),
                New HashSet(Of String)({"PageURL"}),
                New HashSet(Of String)({"ControlTermsOfUse"}),
                New HashSet(Of String)({"ControlPrivacyPolicy"}),
                New HashSet(Of String)({"ControlPurchaseTerm"}),
                New HashSet(Of String)({"ControlLoginName"}),
                New HashSet(Of String)({"ControlStatusOfLogin"}),
                New HashSet(Of String)({"QSPPhone"}),
                New HashSet(Of String)({"ControlQuickCart"}),
                New HashSet(Of String)({"ControlChangePassword"}),
                New HashSet(Of String)({"ControlAgentAccounting"}),
                New HashSet(Of String)({"ActivityEvent"}),
                New HashSet(Of String)({"ControlCulture"}),
                New HashSet(Of String)({"ControlSiteMap"}),
                New HashSet(Of String)({"__MigrationHistory"}),
                New HashSet(Of String)({"PageVersionCulture"}),
                New HashSet(Of String)({"ControlAffiliateSignup"}),
                New HashSet(Of String)({"ControlClientSite"}),
                New HashSet(Of String)({"ControlMessageTracker"}),
                New HashSet(Of String)({"ControlDisplayPhone"}),
                New HashSet(Of String)({"PhoneRSS"}),
                New HashSet(Of String)({"EmailTemplate"}),
                New HashSet(Of String)({"Team"}),
                New HashSet(Of String)({"GeoArea"}),
                New HashSet(Of String)({"ControlRSSReader"}),
                New HashSet(Of String)({"ControlCapturedInput"}),
                New HashSet(Of String)({"ControlCustomerSupportCulture"}),
                New HashSet(Of String)({"ControlCYFQuoteBanner"}),
                New HashSet(Of String)({"Theme"}),
                New HashSet(Of String)({"ControlFlightSearchCulture"}),
                New HashSet(Of String)({"ControlConfirmCulture"}),
                New HashSet(Of String)({"Banned"}),
                New HashSet(Of String)({"AirportAlias"}),
                New HashSet(Of String)({"CountryPhone"}),
                New HashSet(Of String)({"Event"}),
                New HashSet(Of String)({"DisplayPhone"}),
                New HashSet(Of String)({"PINPhone"}),
                New HashSet(Of String)({"PageCategory"}),
                New HashSet(Of String)({"ControlTermsOfUseCulture"}),
                New HashSet(Of String)({"ControlPurchaseTermCulture"}),
                New HashSet(Of String)({"AccessByIP"}),
                New HashSet(Of String)({"CacheControl"}),
                New HashSet(Of String)({"BannedCaptcha"}),
                New HashSet(Of String)({"ControlBookingWiz"}),
                New HashSet(Of String)({"Activity"}),
                New HashSet(Of String)({"ActivityAction"}),
                New HashSet(Of String)({"ActivityService"}),
                New HashSet(Of String)({"ControlCultureCulture"}),
                New HashSet(Of String)({"ControlDealList"}),
                New HashSet(Of String)({"ControlCartCulture"}),
                New HashSet(Of String)({"ControlPrivacyPolicyCulture"}),
                New HashSet(Of String)({"ControlProfile"}),
                New HashSet(Of String)({"ControlNewsletterSignupCulture"}),
                New HashSet(Of String)({"ControlRegister"}),
                New HashSet(Of String)({"ControlFlightQuoteCulture"}),
                New HashSet(Of String)({"ControlFlightSearchResponsiveCulture"}),
                New HashSet(Of String)({"ControlFlightFilterCulture"}),
                New HashSet(Of String)({"ControlFlightModifyCulture"}),
                New HashSet(Of String)({"Privilege"}),
                New HashSet(Of String)({"Setting"}),
                New HashSet(Of String)({"SiteCloneKey"}),
                New HashSet(Of String)({"CulturePhone"}),
                New HashSet(Of String)({"Deal"}),
                New HashSet(Of String)({"DealImport"}),
                New HashSet(Of String)({"ErrorLogging"}),
                New HashSet(Of String)({"UserPrivilege"}),
                New HashSet(Of String)({"Test"}),
                New HashSet(Of String)({"SiteVisit"})
        })

        Return cacheable.Any(Function(hs) hs.SetEquals(hashIncoming))
    End Function
End Class
Coordinator
Jan 25, 2016 at 1:48 AM
Your implementation seems to be incorrect - you have a list of one item sets but affectedEntitySets will have only one entity set for the simplest queries. So, as soon as the query touches more than one table your algorithm won't work. If you want to exclude just one table why don't you do something like:
protected override bool CanBeCached(ReadOnlyCollection<EntitySetBase> affectedEntitySets, string sql, 
            IEnumerable<KeyValuePair<string, object>> parameters)
{
    return !affectedEntitySets.Contains("{entitySetToBeExcluded}"));
}
Also, note that affectedEntitySets are the store entity sets and not the conceptual model entity sets.

Thanks,
Pawel