Simplify multi true or false combined conditions

Question

The issue is that I want to pull specific records from SQL table and display it according to user privileges (which is a case of 5 combined true or false). Is there a way to avoid writing 32 separate checks and also 32 separate SQL select statements for all the cases of true of false for the combined 5 privileges?

ex:

bool priv1 true|false;
bool priv2 true|false;
bool priv3 true|false;
bool priv4 true|false;
bool priv5 true|false;

if (priv1 && !priv2 && !priv3 && !priv4 && !priv5)
{
    string sql = "SELECT * FROM table WHERE priv='1'";
}

and so on....

Edit: I want to mention that one user can have multi privileges and this is the main issue and why i said 32 cases.

Can you organize the privilege into bitwise enum flags? [Similar to the example here](https://stackoverflow.com/questions/7394180/map-bitwise-enum-to-sql-column-value) — The Lyrist, Jun 02 '18 at 01:04
Yes but we still have the same main issue.. the number of possibilities of 5 bit digits or bools .. 32 cases! may be I forgot to mention that one user can have multi privileges but I thought it's obvious. — Sherif Riad, Jun 02 '18 at 01:43
This problem is really hard to solve with the data structures you are using. Consider putting your privileges into an array, list, or dictionary. Then create another list that represents the privileges that are required. From there it would be easy to compare them. Can't do it with independent variables like this, unless you do something *really* weird. — John Wu, Jun 02 '18 at 02:49
if you use a privileges table (in your database), then you could just add the username and get the user privileges from that table. (assuming each user is registered and assigned to one of these privileges. and in your c# just compare the username and see which priv is assigned to, and continue your work from there. — iSR5, Jun 02 '18 at 03:08
Why is there apparently only one column `priv` in the table on the DB? What's its data type and what's the logic behind the value of `priv`? — sticky bit, Jun 02 '18 at 03:18
it was just a demo show to the issue not the real code or data structure.. but i think I reached to an idea to solve it. thanks guys — Sherif Riad, Jun 02 '18 at 05:35
The enum approach can handle a user with multiple privileges fairly easily. https://msdn.microsoft.com/en-us/library/system.flagsattribute(v=vs.110).aspx . from your example above, for someone who has both priv 1 and 5, what should the query be? depending on whether you want to do a union or intersect, you can potentially handle the logic in your query fairly easily without using branching logic. SELECT * FROM table WHERE [bitwise comparison] will do, as long as you can organize the user permission and the priv flag in the data in the enum flag format. Please do share your solution. thanks! — The Lyrist, Jun 06 '18 at 17:13

John Wu · Answer 1 · 2018-06-02T03:26:10.880

Get your privilege values into an array!! If you are stuck with your current variables, you can just do this:

bool[] privileges = new bool[] { priv1, priv2, priv3, priv4, priv4 };

Once they're in there, you can use LINQ to generate an IN clause:

var list = privileges
    .Select
    ( 
        (p,i) => new
        { 
            Index = i + 1, //Need to add 1, since array is zero-based
            Value = p 
        } 
    )
    .Where
    ( 
        p => p.Value 
    )
    .Select
    ( 
        p => string.Format("'{0}'", p.Index)
    );

var sql = string.Format
    (
        "SELECT * FROM table WHERE priv IN ({0}) ",
        string.Join(",", list)
    );

And this will give you a SQL string in this format:

SELECT * FROM table WHERE priv IN ('1','2')

Obligatory notes:

Why is SELECT * considered harmful?

Why avoid dynamic SQL?

score 0 · Accepted Answer · answered Jun 02 '18 at 05:40

Well I think i will answer this after I figured out how to do it by myself, but really thanks guys for your suggestions.

it will be pure SQL only like so:

sql = "SELECT * from requestsTable WHERE priv IN (SELECT priv FROM privilegesTable WHERE userID=@userID)

score -1 · Answer 3 · answered Jun 02 '18 at 02:13

This will need some tweaking, but I think the it get's the idea across. This is essentially how I'd do it in powershell

function Evaluate-Priv {
  param([int]$p)
  return Get-Random -Minimum 0 -Maximum 2
}

[array]$privs = @()

for($i = 1;$i -le 5;$i++){
  switch(Evaluate-Priv($i)) {
    1 { $privs += "$i," }
    default { continue }
  }
}

[string]$SqlCmd = @"
  SELECT * FROM t WHERE t.priv in ($privs)
"@

Write-Output $SqlCmd

results of several runs:

SELECT * FROM t WHERE t.priv in (2, 3, 4, 5,)
SELECT * FROM t WHERE t.priv in (2, 4, 5,)
SELECT * FROM t WHERE t.priv in (1, 4, 5,)

Simplify multi true or false combined conditions

3 Answers3