Longish LINQ query breakes SQLite-parser - simplify?

Question

I'm programming a search for a SQLite-database using C# and LINQ. The idea of the search is, that you can provide one or more keywords, any of which must be contained in any of several column-entries for that row to be added to the results. The implementation consists of several linq-queries which are all put together by union. More keywords and columns that have to be considered result in a more complicated query that way. This can lead to SQL-code, which is to long for the SQLite-parser.

Here is some sample code to illustrate:

IQueryable<Reference> query = null;

if (searchAuthor)
    foreach (string w in words)
    {
        string word = w;

        var result = from r in _dbConnection.GetTable<Reference>()
                     where r.ReferenceAuthor.Any(a => a.Person.LastName.Contains(word) || a.Person.FirstName.Contains(word))
                     orderby r.Title
                     select r;

         query = query == null ? result : query.Union(result);
    }

if (searchTitle)
    foreach (string word in words)
    {
        var result = from r in _dbConnection.GetTable<Reference>()
                     where r.Title.Contains(word)
                     orderby r.Title
                     select r;

        query = query == null ? result : query.Union(result);
    }

//...

Is there a way to structure the query in a way that results in more compact SQL? I tried to force the creation of smaller SQL-statments by calling GetEnumerator() on the query after every loop. But apparently Union() doesn't operate on data, but on the underlying LINQ/SQL statement, so I was generating to long statements regardless.

The only solution I can think of right now, is to really gather the data after every "sub-query" and doing a union on the actual data and not in the statement. Any ideas?

score 0 · Accepted Answer · edited May 23 '17 at 10:33

0

For something like that, you might want to use a PredicateBuilder, as shown in the chosen answer to this question.

edited May 23 '17 at 10:33

Community

1
1

answered Apr 14 '11 at 15:35

MPelletier

16,256
15
86
137

The PredicateBuilder helps a little bit. Thanks for the hint. It doesn't however seem to work like this: var result = from r in _dbConnection.GetTable() where r.ReferenceAuthor.Any(predicate) select r; – B_old Apr 15 '11 at 07:27
Any idea if that is possible somehow? – B_old Apr 15 '11 at 09:23
@B_old I'm still learning LINQ at the moment. I'll get back to you when I get there :) – MPelletier Apr 15 '11 at 14:26

Longish LINQ query breakes SQLite-parser - simplify?

1 Answers1