Dynamic query with linq to entities using PredicateBuilder

Question

I'm trying to run a query on a very simple table but I'm not getting to any result. The table contains an action log with the following columns:

• ID
• ActionTypeID (create, edit or delete)
• EntryID (the id of the created/edited/or deleted entry)
• TableName (where the action was committed)
• ControlName
• Person (who committed the action. nt-user with department)
• Date

What I wanted to get is a list of all EntryIDs with ActionTypeID == 1 and Person (department) ending with one of a dynamic list of strings. The Problem is that only the first action for one EntryID in the action log should be checked to match one of these departments.

I know this sounds pretty weird. This may result from my bad english and from the database designing disability of my former colleague. I don't know which is worse.

My attempt to get the result I wanted is this:

var predicate = PredicateBuilder.False<ActionLog>();
var pOuter = PredicateBuilder.True<ActionLog>();

pOuter = pOuter.And(h => h.TableName.ToUpper() == "Contact".ToUpper());
pOuter = pOuter.And(h => h.ActionType.ID == 1);

foreach (var dep in b.DepartmentList)
{
    predicate = predicate.Or(x => x.Person.EndsWith("/" + dep.Value));
}

pOuter = pOuter.And(predicate.Expand());

mContactIDs = (from h in db.ActionLog
orderby h.Date
select h).AsExpandable().Where(pOuter).Select(x => x.EntryID).Distinct().ToList();

But this returns me all entries where the 'Person' ends with the selected departments. I just want to check the first entry with this EntryID.

I hope anyone understands what I'm trying to translate from confused german to weird english.

oh- by the way, it's .Net 3.5

EDIT I think I have some problems finding the right explanation - I'll try it with some dummy data.

ID | ActionTypeID | EntryID | TableName  | ControlName | Person             | Date
----------------------------------------------------------------------------------------
1  | 1            | 3       | 'Contacts' | NULL        | 'xxxx/department1' | 04/11/2012
2  | 1            | 3       | 'Contacts' | NULL        | 'yyyy/department2' | 04/11/2012
3  | 1            | 5       | 'Contacts' | NULL        | 'yyyy/department2' | 04/13/2012
4  | 1            | 14      | 'Contacts' | NULL        | 'zzzz/department1' | 04/16/2012

In my example i would be searching for log entries with "Person ends with '/department2' created the first occurrence of a new EntryID", "TableName == 'Contacts'" and "ActionTypeID == 1". The result I want to receive would be (only EntryIDs) 5, as this is the only entry where a user of department2 has been the first one Inserting this EntryID with ActionTypeID 1. I know this is stupid and if I had designed the database I wouldn't have done it this way, but now I have to deal with it. If the quantity of departments to query would not be dynamic I would use this snippet:

IQueryable<ActionLog> log = db.ActionLog;
mContactIDs = (from k in db.Contacts
    where (from h in log
        where h.TableName == "Contacts"
        && h.EntryID == k.ID
        && h.ActionType.ID == 1
        orderby h.Date
        select h).FirstOrDefault().Person.EndsWith("/" + b.Department)
    select k.ID).ToList();

But I don't know how to connect those dynamic departments with the stupid design (and multiple ActionTypeID 1 (create) for one entry. I don't even know why the application saves this)

Thank you, Ben.

Answer 1

I think the problem is the captured variable value(ie dep.Value) inside foreach loop. Make a copy of the variable and use that in the expression. The issue is explained here.

foreach (var dep in b.DepartmentList)
{
    var depValue = dep.Value;
    predicate = predicate.Or(x => x.Person.EndsWith("/" + depValue));
}

Answer 2

I decided to use Entity SQL instead.

        List<int> mContactIDs = new List<int>();
        using (var db = new Entities())
        {
            string queryString = @"
                                SELECT  
                                    VALUE H.EntryID
                                FROM    
                                    Entities.ActionLog AS H
                                WHERE 
                                    H.ID IN (
                                        SELECT VALUE TOP (1) Hi.ID
                                        FROM Entities.ActionLog AS Hi
                                        WHERE Hi.ActionType.ID = 1
                                        AND Hi.TableName = 'Kontakt'
                                        AND Hi.EntryID = H.EntryID
                                        ORDER BY Hi.Date    
                                    )
                                {0}
                                GROUP BY H.EntryID";

            List<string> lDepartments = new List<string>();
            foreach (var dep in b.DepartmentList)
            {
                lDepartments.Add(string.Format(" H.Person LIKE '%{0}' ", dep.Value));
            }

            string sDepartmentQuery = string.Empty;
            if (lDepartments.Count > 0)
            {
                sDepartmentQuery = string.Format(" AND ({0}) ", string.Join(" OR ", lDepartments.ToArray()));
            }

            var result = db.CreateQuery<int>(string.Format(queryString, sDepartmentQuery));
            if (result != null && result.Count() > 0)
            {
                mContactIDs = result.ToList();
            }
        }