Boost your Data Layer Performance using Bulk Select with Telerik Data Access

Have you ever found yourself in a situation when you need to retrieve a bunch of database objects that are logically related to values you already collected in-memory? Currently, Telerik Data Access can help you with it through its special handling for the Contains() extension method. The characteristic behind this method is that it effectively overcomes the limitations imposed by the Relational Database servers related to the number of the parameters in the queries (here are the exact numbers for the different backends), while preserving the default behaviour of Telerik Data Access to issue parameterized statements only. This combination practically allows Bulk Select on the server side for the LINQ queries these methods participate in.

Let’s see that in action!

Suppose the following setup: the details about the users of an application are stored in one database and the permissions they have to modify the application’s data are stored in another. The task would be to find out who are the users that can drop a certain database object and who are the users that can delete records. To achieve this with Telerik Data Access, you can apply the following approach:

1. From the Permissions database, you retrieve the user IDs with the necessary rights:

List<int> userIDsDropDatabase = new List<int>(); 
List<int> userIDsDeleteRecodrds = new List<int>();   

//Retrieve the userIDs
using (PermissionsModel dbContext = new PermissionsModel()) 
{
     //Retrieve the users who can drop the database
     userIDsDropDatabase = dbContext.Permissions
                                     .Where(p => p.DBObjectName == "SofiaCarRentalDb"
                                              && p.GrantedPermission == "DROP")
                                     .Select(p => p.UserID).ToList();
     //Retrieve the users who can delete records from any database table
     userIDsDeleteRecodrds = dbContext.Permissions
                                      .Where(p => p.GrantedPermission == "DELETE")
                                      .Select(p => p.UserID).ToList();
}

2. With the help of Contains(), you filter the users in the Users database:

using (UsersModel dbContext = new UsersModel()) 
{     
    List<User> usersDropDatabase = dbContext.Users
         .Where(u => userIDsDropDatabase.Contains(u.UserID)).ToList();

    List<User> userDeleteRecords = dbContext.Users
         .Where(u => userIDsDeleteRecodrds.Contains(u.UserID)).ToList();
}

The first two queries demonstrate the special handling of Contains(). The SQL statements generated for them would be:

--Contains for users who can drop the database
SELECT * 
FROM [User] a
WHERE (a.[UserID] IN (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,
@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20,@p21,@p22,@p23,@p24,@p25,@p26,@p27,
@p28,@p29,@p30,@p31,@p32,@p33,@p34,@p35,@p36,@p37,@p38,@p39,@p40,@p41,@p42,
@p43,@p44,@p45,@p46,@p47,@p48,@p49,@p50,@p51,@p52,@p53,@p54,@p55,@p56,@p57,
@p58,@p59,@p60,@p61,@p62,@p63,@p64,@p65,@p66,@p67,@p68,@p69,@p70,@p71,@p72,
@p73,@p74,@p75,@p76,@p77,@p78,@p79)) 
ORDER BY COL1

And

--Contains for users who can delete records
CREATE TABLE [#TMPF283515A457B4CCA976FD1F2CD] ([UserID] int)
INSERT INTO [#TMPF283515A457B4CCA976FD1F2CD] ([UserID]) VALUES (@p0)
SELECT * 
FROM [User] a
JOIN [#TMPF283515A457B4CCA976FD1F2CD] AS b ON (a.[UserID] = b.[UserID]) 
ORDER BY COL1
DROP TABLE [#TMPF283515A457B4CCA976FD1F2CD]

The difference between the scripts comes from the difference between the numbers of the elements in the userIDsDropDatabase and the userIDsDeleteRecords collections. In the first case, the IDs are 80, while in the other case they are 560. Given the fact that this example uses MS SQL Server, and that MS SQL Server has a limitation of 500 for the allowed parameters in the IN clause, Telerik Data Access takes this limitation into account when the queries are generated and uses a temporary table when the number of the parameter exceeds the limit.

What do you think? Do these methods work for you?

P.S. You can get the sample used here from our Data Access Samples repository in GitHub.