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

Question

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?

If you pass in your array with .AsQueryable() does that work? — NetMage
– NetMage, Commented May 7, 2019 at 18:45

Harald Coppoolse · Accepted Answer · 2019-05-09 12:08:13Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related