Queries with the lists and user-defined types

[stsrki]

Would it be possibe to implement the queries that are using lists with the user-defined types? The problem is that I have a database that is entirely made using the composite keys, that is, every table have two columns that made up the primary key. If i try to write linq query that is using my own lists/types, BLToolkit cannot translate it into the appropriate SQL.

The following example is purely for the testing purposes, just to give you a rough idea of how it can be done.

Linq query (lets pretend that FirstName/LastName is composite key).

var parameters = new Person[]
{ 
    new Person { FirstName = "John", LastName = "Pupkin" },
    new Person { FirstName = "Jane", LastName = "Pupkin" }
};

var query = from p in db.Person
            where
            parameters.Any( x => x.FirstName == p.FirstName && x.LastName == p.LastName )
            select p;

Requierd SQL. Notice that the parameters list is converted to the subquery and every member is selected with the union.

select
    P.PersonID,
    P.FirstName,
    P.LastName,
    P.MiddleName,
    P.Gender
from
    Person P
where
    exists
    (
        select
            T.FirstName,
            T.LastName
        from
        (
            select 'John' as FirstName, 'Pupkin' as LastName union
            select 'Jane' as FirstName, 'Pupkin' as LastName
        ) T
        where
            T.FirstName = P.FirstName and T.LastName = P.LastName
    )

[pebezo]

Have you considered using something like a predicate builder? http://www.albahari.com/nutshell/predicatebuilder.aspx You could build a helper function on top of that that would handle your data structure. The final SQL would / could look like "WHERE (A1=@A1 AND A2=@A2 ... ) OR (B1=@B1 AND B2=@B2 ...) ... "