Databases
StackOverflow2013
Postsdbo
POHow to improve my Entity Framework , to join several database queries into single query
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to improve my Entity Framework , to join several database queries into single query
    text
    copied!<p>I have the following ActionFilter class, to implement my custom authorization system:-</p> <pre><code>[AttributeUsage(AttributeTargets.Class | AttributeTargets.Method, AllowMultiple = false, Inherited = true)] public class CheckUserPermissionsAttribute : ActionFilterAttribute { Repository repository = new Repository(); public string Model { get; set; } public string Action { get; set; } public override void OnActionExecuting(ActionExecutingContext filterContext) { string ADusername = filterContext.HttpContext.User.Identity.Name.Substring(filterContext.HttpContext.User.Identity.Name.IndexOf("\\") + 1); if (!repository.can(ADusername,Model,Action)) { filterContext.Result = new HttpUnauthorizedResult("You cannot access this page"); } base.OnActionExecuting(filterContext); } } </code></pre> <p>The above class will call the following repository method:-</p> <pre><code> public bool can(string user, string Model, string Action) { bool result; bool result2; int size =tms.PermisionLevels.Where(a5 =&gt; a5.Name == Action).SingleOrDefault().PermisionSize; var securityrole = tms.SecurityroleTypePermisions.Where(a =&gt; a.PermisionLevel.PermisionSize &gt;= size &amp;&amp; a.TechnologyType.Name == Model).Select(a =&gt; a.SecurityRole).Include(w=&gt;w.Groups).Include(w2=&gt;w2.SecurityRoleUsers).ToList(); foreach (var item in securityrole) { result = item.SecurityRoleUsers.Any(a =&gt; a.UserName.ToLower() == user.ToLower()); var no = item.Groups.Select(a=&gt;a.TMSUserGroups.Where(a2=&gt;a2.UserName.ToLower() == user.ToLower())); result2 = no.Count() == 1; if (result || result2) { return true; }} return false; } </code></pre> <p>But inside my repository method , I am doing the following:-</p> <ol> <li><p>Query the database and include all the Groups &amp; SecurityRoleUsers when executing the .tolist()</p></li> <li><p>Then filter the returned records insdie the server, based on the foreach loop.</p></li> </ol> <p>But this will cause the following drawbacks:-</p> <ol> <li><p>If I have many Groups and SecurityRoleUsers, then I will be getting them all from the DB, and then filter the result on the server. </p></li> <li><p>And since this code will be executed whenever an action method is called, as it Is a security attribute at the begging of the controller class. So this might not be very efficient.</p></li> </ol> <p>So my question is whether I can join all the queries inside the repository method to be single query , and do all the work on the Database and just return true or false to the server ? The associated tables looks as follow:-</p> <p><img src="https://i.stack.imgur.com/FbVNP.png" alt="enter image description here"></p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload