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:
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();
}
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();
}
--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
--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]
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.