CODEWITHSUNDEEP
c#asp.netlinqlinq-to-sql
Tom Gullen
Tom Gullen

Reputation: 61737

Simplify LINQ query

I have the following query:

db.ObjectTags.Where(c => 
    c.TagID == tagID && 
    (!db.DeletedObjects.Any(d=> d.ForObjectTypeID == c.ForObjectTypeID && d.ForObjectID == c.ForObjectID)
    || !db.DeletedObjects.SingleOrDefault(d => d.ForObjectTypeID == c.ForObjectTypeID && d.ForObjectID == c.ForObjectID).Deleted)
)

Its goal is to return objects that are not in a deleted state.

The table DeletedObjects has two states:

I need to query where either the record doesn't exist, or if it does the deleted value is false.

Is there any way to condense that statement eg with SingleOrDefault()?

Upvotes: 0

Views: 76

Answers (3)

Mehdi Javan
Mehdi Javan

Reputation: 1091

I believe you need to left join between ObjectTags and DeletedObjects. A LINQ query like this:

from objectTag in db.ObjectTags
from deletedObject in db.DeletedObjects
    .Where(deletedObject => deletedObject.ForObjectTypeID == objectTag.ForObjectTypeID && deletedObject.ForObjectID == objectTag.ForObjectID)
    .DefaultIfEmpty()
where deletedObject == null || !deletedObject.Deleted

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460058

You only need one !db.DeletedObjects.Any(...) and no SingleOrDefault

var q = db.ObjectTags
   .Where(c=> c.TagID == tagID && !db.DeletedObjects
       .Any(d => d.Deleted && d.ForObjectTypeID == c.ForObjectTypeID && d.ForObjectID == c.ForObjectID));

Upvotes: 1

jignesh patel
jignesh patel

Reputation: 982

Can you please try this linq query

db.ObjectTags.Where(c => 
    c.TagID == tagID && 
    (db.DeletedObjects.Any(d=> d.ForObjectTypeID == c.ForObjectTypeID && d.ForObjectID == c.ForObjectID && !c.Deleted))
)

Upvotes: 0

Related Questions