How to refactor a linq query to reuse its parts

Question

I have been trying to refactor this query for a while without any luck:

db.Kiosks.Where(kiosk => db.KioskDesignations.Where(
                 q =>
                 q.Kiosk.KioskId == kiosk.KioskId &&
                 (!q.RedesignedAt.HasValue||q.RedesignedAt.Value<= DateTime.Now))
                     .OrderByDescending(q => q.RedesignedAt)
                     .Take(1).Select(q => q.Type.DefinitionId).Contains(id)
                                   );

Here's the problem.Each kiosk has a historical collection of designations and in some parts of the application we want to do something based on its latest state(by checking its Type or Activity or some other data) so this part of the query will be repeated :

db.KioskDesignations.Where(q =>
          q.Kiosk.KioskId == kiosk.KioskId &&
          (!q.RedesignedAt.HasValue || q.RedesignedAt.Value <= DateTime.Now))
               .OrderByDescending(q => q.RedesignedAt).Take(1)

So far I have tried writing this part as a function , a Func and an Expression and none of them are working.Would you please tell me how should I refactor this query so that I can reuse the repeating part ? Thanks a lot.

Answer 1

I finally found a solution :) Since Where method of linq accepts Func<T,bool> then I wrote a function that returns a Func<T,bool> and call it in my Where like this :

db.Kiosks
    .Where(QueryCurrentKioskDesignation(db, d => d.Type.DefinitionId == id))

This function gets a Func as a predicate to filter our kiosk based on its current Designation data. And here's the QueryCurrentKioskDesignation function :

public static Func<Kiosk, bool> QueryCurrentKioskDesignation(DataContext db,
                                                                     Func<KioskDesignation, bool> predicate)
        {
            return k => db.KioskDesignations.Where(q => q.Kiosk.KioskId == k.KioskId &&
                                                            (!q.RedesignedAt.HasValue ||
                                                             q.RedesignedAt.Value <= DateTime.Now))
                                   .OrderByDescending(q => q.RedesignedAt)
                                   .Take(1).Any(predicate);
        }

Update: I noticed that Linq methods return an IEnumerable<T> whenever we use Func<> (It means that Linq invokes the Function right away and returns an IEnumerable) but it would be better to use Expressions to let Linq builds an expression tree that we can execute it later. To achieve this I just changed my QueryCurrentKioskDesignation signature to accept an expression and return one :

public static Expression<Func<Kiosk, bool>> QueryCurrentKioskDesignation(DataContext db,
                                                                     Expression<Func<KioskDesignation, bool>> predicate)

and now I can work with IQueryalbe and get all the data that I want with just a single query to the database and to show you the beauty of it here's the generated query that I get using EFProf

SELECT TOP (20) [Extent1].[KioskId]            AS [KioskId],
                [Extent1].[Code]               AS [Code],
                [Extent1].[Barcode]            AS [Barcode],
                [Extent1].[Notes]              AS [Notes],
                [Extent1].[CheckedAt]          AS [CheckedAt],
                [Extent1].[SearchKeywords]     AS [SearchKeywords],
                [Extent1].[CreatedAt]          AS [CreatedAt],
                [Extent1].[CreatedBy]          AS [CreatedBy],
                [Extent1].[LastEditAt]         AS [LastEditAt],
                [Extent1].[LastEditBy]         AS [LastEditBy],
                [Extent1].[Guild_KioskGuildId] AS [Guild_KioskGuildId]
FROM   [dbo].[Kiosks] AS [Extent1]
WHERE  (EXISTS (SELECT 1 AS [C1]
                FROM   (SELECT TOP (1) [Project1].[Activity_DefinitionId] AS [Activity_DefinitionId]
                        FROM   (SELECT [Extent2].[RedesignedAt]          AS [RedesignedAt],
                                       [Extent2].[Activity_DefinitionId] AS [Activity_DefinitionId]
                                FROM   [dbo].[KioskDesignations] AS [Extent2]
                                WHERE  ([Extent2].[Kiosk_KioskId] = [Extent1].[KioskId])
                                       AND (([Extent2].[RedesignedAt] IS NULL)
                                             OR ([Extent2].[RedesignedAt] <= (SysDateTime())))) AS [Project1]
                        ORDER  BY [Project1].[RedesignedAt] DESC) AS [Limit1]
                WHERE  (CASE
                          WHEN (0 /* @p__linq__0 */ = 1) THEN
                            CASE
                              WHEN (14 = [Limit1].[Activity_DefinitionId]) THEN cast(1 as bit)
                              WHEN (14 <> [Limit1].[Activity_DefinitionId]) THEN cast(0 as bit)
                            END
                          WHEN (14 <> [Limit1].[Activity_DefinitionId]) THEN cast(1 as bit)
                          WHEN (14 = [Limit1].[Activity_DefinitionId]) THEN cast(0 as bit)
                        END) = 1))
       AND (EXISTS (SELECT 1 AS [C1]
                    FROM   (SELECT TOP (1) [Project3].[Type_DefinitionId] AS [Type_DefinitionId]
                            FROM   (SELECT [Extent3].[RedesignedAt]      AS [RedesignedAt],
                                           [Extent3].[Type_DefinitionId] AS [Type_DefinitionId]
                                    FROM   [dbo].[KioskDesignations] AS [Extent3]
                                    WHERE  ([Extent3].[Kiosk_KioskId] = [Extent1].[KioskId])
                                           AND (([Extent3].[RedesignedAt] IS NULL)
                                                 OR ([Extent3].[RedesignedAt] <= (SysDateTime())))) AS [Project3]
                            ORDER  BY [Project3].[RedesignedAt] DESC) AS [Limit2]
                    WHERE  [Limit2].[Type_DefinitionId] = 4 /* @p__linq__1 */))

and that's why I love Linq :)

Answer 2

You can chain Wheres for example:

    private void Something()
    {
            var query = GetStandardWhere(db.Kiosks);
            query = query.Where( //some new criteria);
            return query
                 .OrderByDescending(q => q.RedesignedAt)
                 .Take(1).Select(q => q.Type.DefinitionId).Contains(id)
                               );
    }

    private IQueryable<KioskDesignation> GetStandardWhere(IQueryable<KioskDesignation> query)
    {
        return
            query.Where(
                kiosk =>
                db.KioskDesignations.Where(
                    q =>
                    q.Kiosk.KioskId == kiosk.KioskId &&
                    (!q.RedesignedAt.HasValue || q.RedesignedAt.Value <= DateTime.Now));
    }