Comparing sql with c# list in query?

Question

I have a problem with comparing a value in my sql database with a list of object in my code.

The object does not exist in DB

Example of an object in the list:

{
    public long CompareId
    public bool HasAccess
}

I'm using SQLBuilder in c# And then I want to make a query that says something like this:

In made up code

SELECT * FROM EntityPermission
WHERE EntityPermission.HasAccess = listOfObjects.Where(obj => obj.CompareId == EntityPermission.CompareId).HasAccess

In more made up code but with sql builder

query.WHERE("(EntityPermission.HasAccess = {0})", listOfObjects.Where(obj => obj.CompareId == EntityPermission.CompareId).HasAccess)

I'm fully aware of that I'm mixing c# and sql here, but it was the best way I could explain what I want to accomplish.

In words

I want to find the EntityPermission where the HasAccess column is equal to the HasAccess property of the object where they have the same Id.

Really thankful for all help!

Answer 1

I want to find the EntityPermission where the HasAccess column is equal to the HasAccess property of the object where they have the same Id.

So you have a table EntityPermissions. Every EntityPermission in this table has at least a Boolean property HasAccess, and a primary key in long property Id

Furthermore you have a list of objects, where every object has at least an CompareId and a HasAccess.

If I read your requirement correctly, you want all EntityPermissions with Id that is also a CompareId in your list, and that have equal HasAccess value.

So if your list has values:

{10, false}, {11, true}, {12, false},

And you have EntityPermissiont:

Id  HasAccess 
09  true         don't want this one, Id is not in the list
10  true         don't want this one, Id is in the list, but HasAccess incorrect
11  true         I want this one: Id is in the list, HasAccess is correct
12  false        I want this one: Id is in the list, HasAccess is correct

Normally you would use Where(x => y.Contains(x)) for this. The problem is that with this you can only select on one property.

var checkValues = new
{
    new {CompareId = 10, HasAccess = false},
    new {CompareId = 11, HasAccess = true},
    new {CompareId = 12, HasAccess = false},
}

var result = dbContext.EntityPermissions.Select(entityPermission => new
{
    ValueToCompare = new
    {
         CompareId = entityPermission.Id,
         HasAccess = entityPermission.HasAccess,
    },

    Original = entityPermission,
})

// keep only those selected items that have a ValueToCompare in CheckValues
.Where(selectedItem => checkValues.Contains(selectedItem.ValueToCompare)

// from the remaining items, extract the original EntityPermission
.Select(selectedItem => selectedItem.Original);

Answer 2

What you're looking for is the SQL WHERE...IN () syntax.

If you're using a tool that produces SQL, what you want to do is something like this:

1) get the list of values you want to compare

2) Create a string like the following from them:

"('value1','value2','value3')"

3) then produce a query that looks like this:

SELECT * FROM EntityPermission
WHERE EntityPermission.HasAccess
IN ('value1','value2','value3')

for an ORM like Entity Framework, NHibernate, etc, you can do the following:

var results = db.EntityPermissions
                .Where(x => listOfObjects
                           .Where(obj => obj.CompareId == EntityPermission.CompareId)
                .Select(y => y.HasAccess)
                .Contains(x.HasAccess))

Answer 3

You could do this with a table-valued-parameter and user-defined-type, then inner join:

SELECT * FROM EntityPermission ep
INNER JOIN @foo f ON f.Id = ep.Id AND f.HasAccess = ep.HasAccess

However: UDTs and TVPs are really very awkward to work with; frankly, I'd be tempted to just create two concatenated strings:

string with = string.Join(",", list.Where(x => x.HasAccess).Select(x => x.Id));
string without = string.Join(",", list.Where(x => !x.HasAccess).Select(x => x.Id));

and pass that down as parameters to use with string_split:

SELECT *
FROM EntityPermission
WHERE (Id in (select value from string_split(@with, ',')) and HasAccess = 1)
OR (Id in (select value from string_split(@without, ',')) and HasAccess = 0)

Answer 4

You can first get your sql query in a result table then use LINQ to get your intended values. I know it is not most effective way but it could work.

        public virtual List<YOUR_DTO> ExampleOperation(YOUR_DTO dto)
        {

            sqlText="SELECT * FROM EntityPermission ";
            dbComm = db.GetSqlStringCommand(sqlText);

            DataTable table = this.Database.ExecuteDataSet(dbComm).Tables[0];

            List<YOUR_DTO> result = new List<YOUR_DTO>();
            foreach (DataRow row in table.Rows)
            {
                result.Add(new YOUR_DTO()
                {
                    ...
                });
            }
            //LINQ
            result = result.Where(obj => obj.CompareId == EntityPermission.CompareId).HasAccess;
            return result;
        }