Over a million developers have joined DZone.

Boost Your Data Layer Performance Using Bulk Select with Telerik Data Access

DZone's Guide to

Boost Your Data Layer Performance Using Bulk Select with Telerik Data Access

· Java Zone
Free Resource

Bitbucket is for the code that takes us to Mars, decodes the human genome, or drives your next car. What will your code do? Get started with Bitbucket today, it's free.

Originally written by Ivailo Ivanov

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:

  2. List<int> userIDsDropDatabase =newList<int>();List<int> userIDsDeleteRecodrds =newList<int>();//Retrieve the userIDsusing(PermissionsModel dbContext =newPermissionsModel()){
       //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();}
  3. With the help of Contains(), you filter the users in the Users database:

  4. using(UsersModel dbContext =newUsersModel()){   
        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:

--Containsfor users who can drop the database
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)) 


--Containsfor users who can delete records
CREATE TABLE [#TMPF283515A457B4CCA976FD1F2CD]([UserID]int)
INSERT INTO [#TMPF283515A457B4CCA976FD1F2CD]([UserID]) VALUES (@p0)
FROM [User] a
JOIN [#TMPF283515A457B4CCA976FD1F2CD] AS b ON (a.[UserID]= b.[UserID]) 

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.

Bitbucket is the Git solution for professional teams who code with a purpose, not just as a hobby. Get started today, it's free.


Published at DZone with permission of Doug Winfield, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}