C#•3y ago

A ready-made ef core data search solution

Hi, I need to do a database content search engine. I would like the search engine to look for a word not a sentence. There is something ready? Currently I have contains but it searches for the entire string. I'd like to do it on a word board basis

39 Replies

AlerinOP•3y ago

my example code:

    public async Task<List<Article>> ByTextAsync(List<string> search, Func<IQueryable<Models.Article>, IQueryable<Models.Article>>? parameter = null)
    {
        var query = _context.Article
            .Include(x => x.Details)
            .AsNoTracking()
            .Where(x => 
                x.Details.Content.Contains(search) || 
                x.Details.Title.Contains(search) 
            );

        if (parameter is not null)
            query = parameter(query);

        var articles = await query
            .AsAsyncEnumerable()
            .Select(async x => new
            {
                Article = await _article.GetAsync(x.Id),
            })
            .Select(x => x.Result.Article)
            .ToListAsync();

        return articles;
    }

    public async Task<List<Article>> ByTextAsync(List<string> search, Func<IQueryable<Models.Article>, IQueryable<Models.Article>>? parameter = null)
    {
        var query = _context.Article
            .Include(x => x.Details)
            .AsNoTracking()
            .Where(x => 
                x.Details.Content.Contains(search) || 
                x.Details.Title.Contains(search) 
            );

        if (parameter is not null)
            query = parameter(query);

        var articles = await query
            .AsAsyncEnumerable()
            .Select(async x => new
            {
                Article = await _article.GetAsync(x.Id),
            })
            .Select(x => x.Result.Article)
            .ToListAsync();

        return articles;
    }

Grault•3y ago

            .Where(x => 
                search.Any(term =>
                    x.Details.Content.Contains(term) || 
                    x.Details.Title.Contains(term)
                )
            );

            .Where(x => 
                search.Any(term =>
                    x.Details.Content.Contains(term) || 
                    x.Details.Title.Contains(term)
                )
            );

AlerinOP•3y ago

Did I do something wrong?

Grault•3y ago

Oh, sorry. That's my bad. When you're using LINQ with EF, it turns your calls into expression trees, and converts those into SQL. It doesn't run your lambdas as C#. Let me think about this a bit more.

AlerinOP•3y ago

Ok thank you for help 😄

Grault•3y ago

See whether this works any better:

var query = _context.Article
    .Include(x => x.Details)
    .AsNoTracking()
    .Join(search,
        x => x,
        term => term,
        (x, term) => new
        {
            Entry = x,
            Term = term,
        }
    )
    .Where(o => 
        o.Entry.Details.Content.Contains(o.Term) || 
        o.Entry.Details.Title.Contains(o.Term) 
    )
    .Select(o => o.Entry)
    .Distinct();

var query = _context.Article
    .Include(x => x.Details)
    .AsNoTracking()
    .Join(search,
        x => x,
        term => term,
        (x, term) => new
        {
            Entry = x,
            Term = term,
        }
    )
    .Where(o => 
        o.Entry.Details.Content.Contains(o.Term) || 
        o.Entry.Details.Title.Contains(o.Term) 
    )
    .Select(o => o.Entry)
    .Distinct();

Hmm, actually I need to do a bit more to that... editing... That's definitely not optimized, but I think it will work. If it finds your first term in one of the entries, it will still look for your second in that entry. And your third, etc. Which accomplishes nothing, because the entry will be in the results regardless.

AlerinOP•3y ago

I have a problem with Join: Error CS0411 Unable to infer the type arguments for the method 'Queryable.Join <TOuter, TInner, TKey, TResult> (IQueryable <TOuter>, IEnumerable <TInner>, Expression <Func <TOuter, TKey >>, Expression <Func <TInner, TKey >>, Expression <Func <TOuter, TInner, TResult >>) ”based on usage. Try to explicitly specify type arguments.

AlerinOP•3y ago

Grault•3y ago

What type is AsNoTracking() returning?

AlerinOP•3y ago

IQueryable<Models.Article>

Grault•3y ago

Try: Join<Models.Article, string> That will only change the error message, but it might become more useful.

AlerinOP•3y ago

copilot 😄 Error CS1061 "IQueryable <Article>" does not contain a definition of "Join" and an available extension method "Join" that takes the first argument of type "IQueryable <Article>" was not found (are you missing a using directive or an assembly reference?).

Grault•3y ago

Urgh. Not the change I wanted.

Grault•3y ago

https://learn.microsoft.com/en-us/dotnet/api/system.linq.queryable.join?view=net-6.0

Queryable.Join Method (System.Linq)

Correlates the elements of two sequences based on matching keys.

Grault•3y ago

I'm not sure why we aren't calling that. How about this?

var queryable = _context.Article
    .Include(x => x.Details)
    .AsNoTracking();
var query = Queryable.Join<Models.Article, string>(queryable, search,
        x => x,
        term => term,
        (x, term) => new
        {
            Entry = x,
            Term = term,
        }
    )
    .Where(o => 
        o.Entry.Details.Content.Contains(o.Term) || 
        o.Entry.Details.Title.Contains(o.Term) 
    )
    .Select(o => o.Entry)
    .Distinct();

var queryable = _context.Article
    .Include(x => x.Details)
    .AsNoTracking();
var query = Queryable.Join<Models.Article, string>(queryable, search,
        x => x,
        term => term,
        (x, term) => new
        {
            Entry = x,
            Term = term,
        }
    )
    .Where(o => 
        o.Entry.Details.Content.Contains(o.Term) || 
        o.Entry.Details.Title.Contains(o.Term) 
    )
    .Select(o => o.Entry)
    .Distinct();

Sorry, I had to edit that one too.

AlerinOP•3y ago

CS0305 Use generic element method "Queryable.Join <TOuter, TInner, TKey, TResult> (IQueryable <TOuter>, IEnumerable <TInner>, Expression <Func <TOuter, TKey >>, Expression <Func <TInner, TKey >>, Expression <Func <TOuter, TInner, TResult >>) "requires arguments of type" 4 " This is the bug, I've seen a similar solution somewhere on stackoverflow before but can't find it.

Grault•3y ago

Requires arguments of type... 4? ??? Oh, gotcha.

AlerinOP•3y ago

Yes, I have no idea what this is about

        [DynamicDependency("Join`4", typeof(Enumerable))]
        public static IQueryable<TResult> Join<TOuter, TInner, TKey, TResult>(this IQueryable<TOuter> outer, IEnumerable<TInner> inner, Expression<Func<TOuter, TKey>> outerKeySelector, Expression<Func<TInner, TKey>> innerKeySelector, Expression<Func<TOuter, TInner, TResult>> resultSelector)
        {
            ArgumentNullException.ThrowIfNull(outer);
            ArgumentNullException.ThrowIfNull(inner);
            ArgumentNullException.ThrowIfNull(outerKeySelector);
            ArgumentNullException.ThrowIfNull(innerKeySelector);
            ArgumentNullException.ThrowIfNull(resultSelector);

            return outer.Provider.CreateQuery<TResult>(
                Expression.Call(
                    null,
                    CachedReflectionInfo.Join_TOuter_TInner_TKey_TResult_5(typeof(TOuter), typeof(TInner), typeof(TKey), typeof(TResult)), outer.Expression, GetSourceExpression(inner), Expression.Quote(outerKeySelector), Expression.Quote(innerKeySelector), Expression.Quote(resultSelector)));
        }

        [DynamicDependency("Join`4", typeof(Enumerable))]
        public static IQueryable<TResult> Join<TOuter, TInner, TKey, TResult>(this IQueryable<TOuter> outer, IEnumerable<TInner> inner, Expression<Func<TOuter, TKey>> outerKeySelector, Expression<Func<TInner, TKey>> innerKeySelector, Expression<Func<TOuter, TInner, TResult>> resultSelector)
        {
            ArgumentNullException.ThrowIfNull(outer);
            ArgumentNullException.ThrowIfNull(inner);
            ArgumentNullException.ThrowIfNull(outerKeySelector);
            ArgumentNullException.ThrowIfNull(innerKeySelector);
            ArgumentNullException.ThrowIfNull(resultSelector);

            return outer.Provider.CreateQuery<TResult>(
                Expression.Call(
                    null,
                    CachedReflectionInfo.Join_TOuter_TInner_TKey_TResult_5(typeof(TOuter), typeof(TInner), typeof(TKey), typeof(TResult)), outer.Expression, GetSourceExpression(inner), Expression.Quote(outerKeySelector), Expression.Quote(innerKeySelector), Expression.Quote(resultSelector)));
        }

Grault•3y ago

Join<TOuter,TInner,TKey,TResult>
    (IQueryable<TOuter>,
     IEnumerable<TInner>,
     Expression<Func<TOuter,TKey>>,
     Expression<Func<TInner,TKey>>,
     Expression<Func<TOuter,TInner,TResult>>)

Join<TOuter,TInner,TKey,TResult>
    (IQueryable<TOuter>,
     IEnumerable<TInner>,
     Expression<Func<TOuter,TKey>>,
     Expression<Func<TInner,TKey>>,
     Expression<Func<TOuter,TInner,TResult>>)

So it wants to know what type we're using to compare the outer and inner, and what type we're returning. The problem with the join that I wrote above is that I intended to just stuff both values in an object (in SQL, a temporary table, which come to think of it may not be standard SQL and therefore not available in LINQ) rather than comparing them. @Alerin

var queryable = _context.Article
    .Include(x => x.Details)
    .AsNoTracking();
var query = from x in queryable
            join term in search
            on x.Details.Content.Contains(term) || 
                x.Details.Title.Contains(term)
            select x;
query = query.Distinct();

var queryable = _context.Article
    .Include(x => x.Details)
    .AsNoTracking();
var query = from x in queryable
            join term in search
            on x.Details.Content.Contains(term) || 
                x.Details.Title.Contains(term)
            select x;
query = query.Distinct();

LINQ query syntax is not my strong suit, and of course this is untested.

AlerinOP•3y ago

Thanks for your help, I'll have a look. I am also looking to see if Linq can do it.

AlerinOP•3y ago

Error CS1937 The name "term" is outside the range of the left side of the equality operator. Consider swapping expressions on both sides of the equality operator.

Grault•3y ago

OK that's a new one. What type does it say term is?

AlerinOP•3y ago

string

AlerinOP•3y ago

AlerinOP•3y ago

hmm

Grault•3y ago

And x.Details.Content?

AlerinOP•3y ago

https://github.com/ninjanye/SearchExtensions I found a library here but it doesn't seem to be updated.

GitHub

GitHub - ninjanye/SearchExtensions: Library of IQueryable extension...

Library of IQueryable extension methods to perform searching - GitHub - ninjanye/SearchExtensions: Library of IQueryable extension methods to perform searching

AlerinOP•3y ago

yes, x.Details.Content and Title string

Grault•3y ago

Ugh. I give up on doing it the ideal way.

var query = _context.Article
    .Include(x => x.Details)
    .AsNoTracking()
    .AsEnumerable()
    .Where(x =>
        search.Any(term => 
            x.Details.Content.Contains(term) || 
            x.Details.Title.Contains(term)
        )
    );

var query = _context.Article
    .Include(x => x.Details)
    .AsNoTracking()
    .AsEnumerable()
    .Where(x =>
        search.Any(term => 
            x.Details.Content.Contains(term) || 
            x.Details.Title.Contains(term)
        )
    );

That will load all the articles into memory and then filter them. AsEnumerable manages that difference. I wanted to filter in the database, but may not be possible with the restrictions of IQueryable.

AlerinOP•3y ago

I'm surprised that ef core doesn't support this by default.

AlerinOP•3y ago

what is "o"?

Grault•3y ago

I may just not be aware of some technique. I felt like it should work, too. Ah, shoot. Edited.

AlerinOP•3y ago

I think it works, thank you so much for your help. I think I will have to think about elastic search and look for results there 😄

AlerinOP•3y ago

Ready code as if someone was looking.

Grault•3y ago

You can simplify those two Select calls into one. You would need parens around the await, though. Or a separate variable.

PontiacGTX•3y ago

if you call AsAsyncEnumerable you will load that on memory whilel it is selecting

AlerinOP•3y ago

Will there be a performance issue?

PontiacGTX•3y ago

maybe copare suing the AsAsync Enumerable below the where above the first select also why not select the article before hand... so you dont have to query twice the article

Gaming

Programming

A ready-made ef core data search solution