CODEWITHSUNDEEP
sqllinqobject
Bernd Morgeneyer
Bernd Morgeneyer

Reputation: 109

How can I write a method to build a query both for SQL and an array?

I have a method BuildQuery that dynamically generates a query. It works well for SQL:

    static MyType[] GetDataFromDB(MyDataContext db, string city, string district, string region, string country, string zip)
    {
        var q = BuildQuery(db.MyTable.AsQueryable(), city, district, region, country, zip);
        return q.ToArray();
    }

    private static IQueryable<MyType> BuildQuery(IQueryable<MyType> q, string city, string district, string region, string country, string zip)
    {
        if (!string.IsNullOrEmpty(city))
            q = q.Where(p => p.City.Contains(city));
        if (!string.IsNullOrEmpty(district))
            q = q.Where(p => p.District.Contains(district));
        if (!string.IsNullOrEmpty(zip))
            q = q.Where(p => p.Zip == zip);
        if (!string.IsNullOrEmpty(region))
            q = q.Where(p => p.Region.Contains(region));
        if (!string.IsNullOrEmpty(country))
            q = q.Where(p => p.Country == country);
        return q;
    }

(Actually, this query is a bit more compicated.) This works well building an SQL query with some LIKE. Now I would like to use the same query for an array of MyType:

    MyType[] SelectFromResult(MyType[] loc, string city, string district, string region, string country, string zip)
    {
        var q = BuildQuery(loc, city, district, region, country, zip);
        return q.ToArray();
    }

Of coarse this does not compile because MyType[] is an IEnumerable<MyType>, not an IQueryable<MyType>. Changing the type of the first argument in BuildQuery to IEnumerable<MyType>, compiles and works for arrays but won't build the SQL query.

I guess I could make BuildQuery a generic method, but how? Any ideas?

Upvotes: 3

Views: 61

Answers (1)

Harald Coppoolse
Harald Coppoolse

Reputation: 30464

One of the nice things about LINQ is that you can concatenate (link) the functions after each other. This is possible because of the introduction of extension methods.

If you change your BuildQuery function slightly, you can use it as any LINQ function. See extension methods demystified

 private static IQueryable<MyType> BuildQuery(this IQueryable<MyType> q, 
     string city, string district, string region, string country, string zip)
{
    ...

Note the word this before IQueryable. This allows you to put the first parameter of the method (the IQueryable) before the method call.

Back to your question

Any IEnumerable<TSource> can be transformed to an IQueryable<TSource> using AsQueryable(), so also a TSource[]

MyType[] loc = ...
IQueryable<MyType> myQueryable = loc.AsQueryable()
                                    .BuildQuery(city, district, ....);

Well maybe you should give it a proper name: how about ToQuery``?

To execute the query, do what you want to do with it: ToList / ToArray / Count / FirstOrDefault / ...

By the way, when creating LINQ like functions, it is usually better to return IEnumerable / IQueryable instead of List / Array / etc, unless you really know that your caller wants the complete sequence. It would be a waste if you would call ToList() and your caller only wants FirstOrDefault()

Upvotes: 1

Related Questions