A Ka Sup Posts - Answer Overflow

A Ka Sup

•Created by A Ka Sup on 10/14/2024 in #help

Join to other table in LINQKit Predicate

hello, assume that i have a query

c#
var allClasses = from s in context.Classes select new ClassView {
    Id = s.Id,
    Name = s.Name,
};
// ClassView
public class ClassView {
    public int Id { get; set; }
    public string? Name { get; set; }
    public List<SubjectView> Subjects { get; set; }
}
// SubjectView
public class SubjectView {
    public int Id { get; set; }
    public string? Name { get; set; }
}

c#
var allClasses = from s in context.Classes select new ClassView {
    Id = s.Id,
    Name = s.Name,
};
// ClassView
public class ClassView {
    public int Id { get; set; }
    public string? Name { get; set; }
    public List<SubjectView> Subjects { get; set; }
}
// SubjectView
public class SubjectView {
    public int Id { get; set; }
    public string? Name { get; set; }
}

The Subject connect to Class through the table ClassSubject. Due to some reasons, i cannot get the list subjects in the allClasses LINQ, so i write this

c#
var listClasses = await allClasses.ToListAsync();
foreach(var c in listClasses) {
    c.Subjects = await (from s in ClassSubject where s.ClassId = s.Id join x in context.Subject on s.SubjectId equals x.Id select new SubjectView {
        Id = x.Id,
        Name = x.Name,
    }).ToListAsync();
}

c#
var listClasses = await allClasses.ToListAsync();
foreach(var c in listClasses) {
    c.Subjects = await (from s in ClassSubject where s.ClassId = s.Id join x in context.Subject on s.SubjectId equals x.Id select new SubjectView {
        Id = x.Id,
        Name = x.Name,
    }).ToListAsync();
}

so here is my question, when i find the class by some subjects name, like ["Ma", "Li"], i build a predicate with LINQKit, it should be something like this

var predicate = LINQKit.PredicateBuilder.New<ClassView>(false);
foreach(var q in querySubject) {
    predicate = predicate.Or(s => s.Name.Contains(q));
}
allClasses = allClasses.Where(predicate);

var predicate = LINQKit.PredicateBuilder.New<ClassView>(false);
foreach(var q in querySubject) {
    predicate = predicate.Or(s => s.Name.Contains(q));
}
allClasses = allClasses.Where(predicate);

But it's wrong, the func in Or()belongs to Class, not Subject. i need this predicate runs before the listClasses is defined, but i don't know how to join to the table Subject. Can anyone please help me. Thank you!

1 replies

CC#

•Created by A Ka Sup on 9/6/2023 in #help

❔ Cannot change table from second call

hello, according to this document https://learn.microsoft.com/en-us/ef/core/modeling/dynamic-model, i want to get dynamic model so i create a class that inherited from IModelCacheKeyFactory

public class CustomModelCacheKeyFactory : IModelCacheKeyFactory {
    public object Create(DbContext context) => new CustomModelCacheKey(context);
}

public class CustomModelCacheKey {
    (
        Type ContextType,
        string TB1TableName,
        string TB2TableName
    ) key;
    public CustomModelCacheKey(DbContext context) {
        key.ContextType = context.GetType();
        key.TB1TableName = (context as DatabaseContext)?.TB1TableName;
        key.TB2TableName = (context as DatabaseContext)?.TB2TableName;
    }
    public override int GetHashCode() => key.GetHashCode();
    public override bool Equals(object obj) => obj is CustomModelCacheKey other && key.Equals(other.key);
}

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
     optionsBuilder.ReplaceService<IModelCacheKeyFactory, CustomModelCacheKeyFactory>();
 }

public class CustomModelCacheKeyFactory : IModelCacheKeyFactory {
    public object Create(DbContext context) => new CustomModelCacheKey(context);
}

public class CustomModelCacheKey {
    (
        Type ContextType,
        string TB1TableName,
        string TB2TableName
    ) key;
    public CustomModelCacheKey(DbContext context) {
        key.ContextType = context.GetType();
        key.TB1TableName = (context as DatabaseContext)?.TB1TableName;
        key.TB2TableName = (context as DatabaseContext)?.TB2TableName;
    }
    public override int GetHashCode() => key.GetHashCode();
    public override bool Equals(object obj) => obj is CustomModelCacheKey other && key.Equals(other.key);
}

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
     optionsBuilder.ReplaceService<IModelCacheKeyFactory, CustomModelCacheKeyFactory>();
 }

If i want to change table, i just call

_context.TB1TableName = "TB1_Other";

_context.TB1TableName = "TB1_Other";

and when i call _context.Set<TB1>() or _context.Database.GetDbConnection(), the CustomModelCacheKey will run and the table will be changed from TB1 to TB1_Other. Basically, it works perfectly. But now the problem is, when i do this

_context.TB1TableName = "TB1_Other";
var tb1 = _context.Set<TB1>();
_context.TB2TableName = "TB2_Other";
var tb2 = _context.Set<TB2>();

_context.TB1TableName = "TB1_Other";
var tb1 = _context.Set<TB1>();
_context.TB2TableName = "TB2_Other";
var tb2 = _context.Set<TB2>();

The CustomModelCacheKey will be fired only once on line var tb1 = _context.Set<TB1>(); , and not on line var tb2 = _context.Set<TB2>(); . So, from model TB1 and TB2, i will get data from table TB1_OTher and TB2 How can i change table from second line var tb2 = _context.Set<TB2>(); like above

6 replies

Gaming

Programming