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!

What is listOfObjects? is it another entity in your db? you could just do a join and get the data you need. — raven, Jun 02 '20 at 09:37
It's not another entity in my db, it comes from an input. So it's just a value that lives in that moment in code — LittleMygler, Jun 02 '20 at 09:38
What you are trying to do here essentially requires an inner join to a table variable or table valued parameter; the awkward bit is essentially: getting your `listOfObjects` down to the DB - how bit is `listOfObjects`? there's a lot of difference depending on whether this could be 20 vs 2000 vs 200000 — Marc Gravell, Jun 02 '20 at 09:40
the list of object can be around 500 entities potentially more — LittleMygler, Jun 02 '20 at 09:41

score 1 · Accepted Answer · answered Jun 02 '20 at 10:19

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);

score 0 · Answer 2 · answered Jun 02 '20 at 09:45

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))

score 0 · Answer 3 · answered Jun 02 '20 at 09:46

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)

score 0 · Answer 4 · answered Jun 02 '20 at 09:51

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;
        }

Comparing sql with c# list in query?

4 Answers4