Kamil Pisz Posts - Answer Overflow

Kamil Pisz

•Created by Kamil Pisz on 4/25/2024 in #help

Search in database for more then one column (EFCore, Postgresql)

Hello

if (!string.IsNullOrWhiteSpace(query.Search))
 {
     var searchTxt = $"%{query.Search}%";
     queryDb = queryDb.Where(e =>
         (!string.IsNullOrEmpty(e.Name.FirstName) && EFCore.Functions.ILike(e.Name.FirstName, searchTxt)) ||
         (!string.IsNullOrEmpty(e.Name.LastName) && EFCore.Functions.ILike(e.Name.LastName, searchTxt)));
 }

if (!string.IsNullOrWhiteSpace(query.Search))
 {
     var searchTxt = $"%{query.Search}%";
     queryDb = queryDb.Where(e =>
         (!string.IsNullOrEmpty(e.Name.FirstName) && EFCore.Functions.ILike(e.Name.FirstName, searchTxt)) ||
         (!string.IsNullOrEmpty(e.Name.LastName) && EFCore.Functions.ILike(e.Name.LastName, searchTxt)));
 }

in Employee table i have column: FirstName and LastName those examples in search query should work - John - Smith - Smith John - John Smith - John S my first thought (from gpt chat 😉 ) is to split query like :

var searchTerms = query.Search.Split(' '); // split the search phrase into individual words
    queryDb = queryDb.Where(e =>
        searchTerms.Any(term => 
            EFCore.Functions.ILike(e.Name.FirstName ?? string.Empty, $"%{term}%") || 
            EFCore.Functions.ILike(e.Name.LastName ?? string.Empty, $"%{term}%")
        )
    );

var searchTerms = query.Search.Split(' '); // split the search phrase into individual words
    queryDb = queryDb.Where(e =>
        searchTerms.Any(term => 
            EFCore.Functions.ILike(e.Name.FirstName ?? string.Empty, $"%{term}%") || 
            EFCore.Functions.ILike(e.Name.LastName ?? string.Empty, $"%{term}%")
        )
    );

What u think about this ? Problem, what if add filter for example Location but dont want split search query then

4 replies

CC#

•Created by Kamil Pisz on 12/13/2022 in #help

Looking solution for get query from _dbContext.SaveChangesAsync() for save logs and easier debugging

Hello, i have a .net core 6.0 app that save entites in MSSQL database with EfCORE 6.0 so my function is something like

var newInvoice = new Invoice();
newInvoice.Name = "2022/12/12"
newInvoice.Value = 5m;
await _dbContext.Invoices.AddAsync(newInvoice);
await _SaveChangesAsync();

var newInvoiceProduct = new InvoiceProduct();
newInvoiceProduct.Name = "toy1"
newInvoiceProduct.Value = 5m;
await _dbContext.InvoicesProduct.AddAsync(newInvoiceProduct);
await _SaveChangesAsync();

var newInvoice = new Invoice();
newInvoice.Name = "2022/12/12"
newInvoice.Value = 5m;
await _dbContext.Invoices.AddAsync(newInvoice);
await _SaveChangesAsync();

var newInvoiceProduct = new InvoiceProduct();
newInvoiceProduct.Name = "toy1"
newInvoiceProduct.Value = 5m;
await _dbContext.InvoicesProduct.AddAsync(newInvoiceProduct);
await _SaveChangesAsync();

What i want to do: every Add/Modify query that going to DB should create new Log in my Logging table

var newInvoice = new Invoice();
newInvoice.Name = "2022/12/12"
newInvoice.Value = 5m;
await _dbContext.Invoices.AddAsync(newInvoice);
await _SaveChangesAsync();

//Save logs what SQL send to database or save expcetion/error message
var newInvoiceLog = new InvoiceLog();
newInvoiceLog.InvoiceId = newInvoice.Id; // id of new created entity
newInvoiceLog.RawQuery = //<----- that i missing, single string that going to DB like Logging to debug console

var newInvoice = new Invoice();
newInvoice.Name = "2022/12/12"
newInvoice.Value = 5m;
await _dbContext.Invoices.AddAsync(newInvoice);
await _SaveChangesAsync();

//Save logs what SQL send to database or save expcetion/error message
var newInvoiceLog = new InvoiceLog();
newInvoiceLog.InvoiceId = newInvoice.Id; // id of new created entity
newInvoiceLog.RawQuery = //<----- that i missing, single string that going to DB like Logging to debug console

5 replies

CC#

•Created by Kamil Pisz on 11/18/2022 in #help

Switch order when two cases meet condition, How precedence work here?

switch (name)
            {
                case string a when a.Contains("John Dee"):
                    DoSomething1;
                    break;
              case string a when a.Contains("John"):
                    DoSomething1;
                    break;
            case string a when a.Equeals("John Dee"):   //equals
                    DoSomething1;
                    break;
}

switch (name)
            {
                case string a when a.Contains("John Dee"):
                    DoSomething1;
                    break;
              case string a when a.Contains("John"):
                    DoSomething1;
                    break;
            case string a when a.Equeals("John Dee"):   //equals
                    DoSomething1;
                    break;
}

IF name = "John Dee" which one case hit first ?

7 replies

CC#

•Created by Kamil Pisz on 10/10/2022 in #help

[EFCore] add many Parent entities with Child entities, How to connect them in single SaveChanges

Hello, im trying to sync data in my database with data in external source there is a example that i have list of MainCategories (parent) and Categories(child) !! code screen from IDE for better visibility in comments

var listOfParentCategoriesFromExternalSource;

var newListOfParentCategoryToAdd = new List<ParentCategory>();
foreach(parent in listOfParentCategoriesFromExternalSource)
{
    var newParentCategory = ParentCategory();
    newParentCategory.Name = "Main Category Name";

    var newListChildCategoryToAdd = new List<ChildCategory>();
    foreach(child in parent)
    {
        var newChildCategory = ChildCategory();
        newChildCategory.Name ="Child Category Name"
        newChildCategory.ParentId = ??   // < --------- How to make connection ?

        newListChildCategoryToAdd.Add(newChildCategory)  //----Prepare list to AddRange

    }
    newListOfParentCategoryToAdd.Add(newParentCategory) //----Prepare list to AddRange
}
await _dbContext.ParentsCategory.AddRangeAsync(newListOfParentCategoryToAdd);
await _dbContext.ParentsCategory.AddRangeAsync(newListChildCategoryToAdd);
await _dbContext.SaveChangesAsync();

var listOfParentCategoriesFromExternalSource;

var newListOfParentCategoryToAdd = new List<ParentCategory>();
foreach(parent in listOfParentCategoriesFromExternalSource)
{
    var newParentCategory = ParentCategory();
    newParentCategory.Name = "Main Category Name";

    var newListChildCategoryToAdd = new List<ChildCategory>();
    foreach(child in parent)
    {
        var newChildCategory = ChildCategory();
        newChildCategory.Name ="Child Category Name"
        newChildCategory.ParentId = ??   // < --------- How to make connection ?

        newListChildCategoryToAdd.Add(newChildCategory)  //----Prepare list to AddRange

    }
    newListOfParentCategoryToAdd.Add(newParentCategory) //----Prepare list to AddRange
}
await _dbContext.ParentsCategory.AddRangeAsync(newListOfParentCategoryToAdd);
await _dbContext.ParentsCategory.AddRangeAsync(newListChildCategoryToAdd);
await _dbContext.SaveChangesAsync();

Gaming

Programming