Linq, doing Join comparing with Like
I have a question regarding Linq. As I have observed some codes, they usually use as for example:
var _s = (from p in exemplo1
join q in exemplo2 on p.blabla equals q.blablabla
I would like to know if a structure like for example that I will show below is valid and how can I create it:
var _s = (from p in exemplo1
join q in exemplo2 on SqlMethods.Like(p.blabla, "%" + q.blablabla + "%")
2
Author: Daniel Nicodemos, 2016-03-10
3 answers
After a few minutes of kick, I used this solution and it worked.
var _s = (from p in exemplo1
from q in exemplo2
where
SqlMethods.Like(p.blabla, "%" + q.blablabla + "%")
1
Author: Daniel Nicodemos, 2016-03-10 17:38:43
You can use the Contains Method to do this.
using (var DBCtx = new RohrdbContext())
{
var s = (from p in DBCtx.exemplo1
from q in DBCtx.exemplo2
where q.blablablabla.Contains(p.blabla)
select p);
// eu particulamente prefiro dessa forma.
var s2 = DBCtx.exemplo1
.Select(p => new
{
P = p,
C = DBCtx.exemplo2.FirstOrDefault(C => C.Id = p.Id),
})
.Where(p => p.P.blablablabla.Contains(p.C.blabla));
//.Where(p => p.P.blablablabla.StartsWith(p.C.blabla)); // Inicio da string
//.Where(p => p.P.blablablabla.EndsWith(p.C.blabla)); // fim da string
}
1
Author: Marco Souza, 2016-03-24 14:13:52
Like Linq / SQL Extension
Class LikeExtension
Tested on .NET 5
public static class LikeExtension {
private static string ColumnDataBase<TEntity, TKey>(IModel model, Expression<Func<TEntity, TKey>> predicate) where TEntity : class {
ITable table = model
.GetRelationalModel()
.Tables
.First(f => f
.EntityTypeMappings
.First()
.EntityType == model
.FindEntityType(predicate
.Parameters
.First()
.Type
));
string column = (predicate.Body as MemberExpression).Member.Name;
string columnDataBase = table.Columns.First(f => f.PropertyMappings.Count(f2 => f2.Property.Name == column) > 0).Name;
return columnDataBase;
}
public static IQueryable<TEntity> Like<TEntity, TKey>(this DbContext context, Expression<Func<TEntity, TKey>> predicate, string text) where TEntity : class {
string columnDataBase = ColumnDataBase(context.Model, predicate);
return context.Set<TEntity>().FromSqlRaw(context.Set<TEntity>().ToQueryString() + " WHERE [" + columnDataBase + "] LIKE {0}", text);
}
public static async Task<IEnumerable<TEntity>> LikeAsync<TEntity, TKey>(this DbContext context, Expression<Func<TEntity, TKey>> predicate, string text, CancellationToken cancellationToken) where TEntity : class {
string columnDataBase = ColumnDataBase(context.Model, predicate);
return await context.Set<TEntity>().FromSqlRaw(context.Set<TEntity>().ToQueryString() + " WHERE [" + columnDataBase + "] LIKE {0}", text).ToListAsync(cancellationToken);
}
public static async Task<IEnumerable<TEntity>> LikeAsync<TEntity, TKey>(this IQueryable<TEntity> query, Expression<Func<TEntity, TKey>> predicate, string text, CancellationToken cancellationToken) where TEntity : class {
DbSet<TEntity> entities = query as DbSet<TEntity>;
string columnDataBase = ColumnDataBase(entities.EntityType.Model, predicate);
return await entities.FromSqlRaw(query.ToQueryString() + " WHERE [" + columnDataBase + "] LIKE {0}", text).ToListAsync(cancellationToken);
}
public static IQueryable<TEntity> Like<TEntity, TKey>(this IQueryable<TEntity> query, Expression<Func<TEntity, TKey>> predicate, string text) where TEntity : class {
DbSet<TEntity> entities = query as DbSet<TEntity>;
string columnDataBase = ColumnDataBase(entities.EntityType.Model, predicate);
return entities.FromSqlRaw(query.ToQueryString() + " WHERE [" + columnDataBase + "] LIKE {0}", text);
}
}
Repository
public async Task<IEnumerable<TEntity>> LikeAsync<TKey>(Expression<Func<TEntity, TKey>> predicate, string text, CancellationToken cancellationToken) {
return await context.LikeAsync(predicate, text, cancellationToken);
}
public IQueryable<TEntity> Like<TKey>(Expression<Func<TEntity, TKey>> predicate, string text) {
return context.Like(predicate, text);
}
Use
IQueryable<CountryEntity> result = countryRepository
.Like(k => k.Name, "%Bra[sz]il%") /*Use Sync*/
.Where(w => w.DateRegister < DateTime.Now) /*Example*/
.Take(10); /*Example*/
Or
IEnumerable<CountryEntity> result = await countryRepository
.LikeAsync(k => k.Name, "%Bra[sz]il%", cancellationToken); /*Use Async*/
Or
IQueryable<CountryEntity> result = context.Countries
.Like(k => k.Name, "%Bra[sz]il%")
.Where(w => w.Name != null); /*Example*/
Or
List<CountryEntity> result2 = await context.Countries
.Like(k => k.Name, "%Bra[sz]il%")
.Where(w => w.Name != null) /*Example*/
.ToListAsync(); /*Use Async*/
Or
IEnumerable<CountryEntity> result3 = await context.Countries
.Where(w => w.Name != null)
.LikeAsync(k => k.Name, "%Bra[sz]il%", cancellationToken); /*Use Async*/
1
Author: Tiago Santos, 2020-11-14 15:22:17