Call new function based on returned value from database

Question

Is there a way to call a function based on the output from the table? Meaning, I'm calling my table and there are at times the column may have a string in it like this { MM;Jan:Q1,Q2,Q3;Feb:Q1,Q2,A3;March;Apr;May.... }, this string can contain { MM, MMM, M, or MMMM } and there are times, it can be NULL and then there are times it will have a select statement in it such as { select column1 from table b} In my function that is using LINQ is there a way to look at that string and

If the string contains { MM } parse that out and return it back to the UI? for this example such as

Jan

Feb

March

.....

and I need to group it so I see it like this: Jan Q1 Q2 Q3 Feb Q1 Q2 Q3 etc and if has a query in it, pass that query to a new function that will execute that query?

Here is my current code

public async Task<ActionResult> GetReportConditions(String region)
{
        var output = _db.GetSalesRegions.where(x => x.region == region)
        .Select(x => new
            {
                salesData = x.SalesReportFilters.Contains("MM") ? parseString(I need to pass the string here to parse) : executeQuery(I need to grab the string and send it to the new function to execute the query and return those results to the UI)  //this is where Im stuck
            }).ToArrayAsync();

        return new JsonResults(output); 
 
}

How can I call the functions and pass in the string to either parse or execute the query, and return the results to the UI?

Answer 1

Yes you can do that but you'll have to write the translation code yourself. But I will start by saying that I wouldn't recommend doing this in the controller itself. If you need any sort of reuse then your controller should call an intermediate "service" class to handle the work of talking to the DB. If you absolutely must do it directly on the context then consider adding an extension method that handles the details for you.

Let's start with the simple case. If you need to "transform" a value in the DB to something else then a simple property on the base type is sufficient.

public class SalesRegion
{
   //Backed by DB
   public string SalesReportFilters { get; set; }

   public string FormattedFilter 
   {
      get {
         if (SalesReportFilters.Contains("MM")) 
            //Parse data and return it

         //Return original value
      }
   }
}

public async Task<ActionResult> GetReportConditions(String region)
{
   var output = _db.GetSalesRegions.where(x => x.region == region)
                            .Select(x => new
            {
                salesData = x.FormattedFilter
            }).ToArrayAsync();

  return new JsonResults(output);  
}

If you need to do work that requires more than the instance itself holds then you'll have to write that out yourself. An extension method can be used here to make it reusable elsewhere.

public static class SalesRegionExtensions
{
   public static IEnumerable<SalesRegion> GetSalesRegionsWithFilters ( this DbContext context, string region )   
   {
      var items = _db.GetSalesRegions.Where(x => x.region == region);

      foreach(var item in items)
      {
          //Set the "salesData" on the SalesRegion based upon the condition
          item.SalesData = item.SalesReportFilter;
          if (/*whatever condition determines you need to run a query*/)    
          {
             var results = _db.GetWhateverData(...);
             item.SalesData = //hook up results
          };
      };

      return items;
   }
}

The caveat here is that you can attach the results of the extra query to the original region data that is being returned. If that isn't an option then you might need to introduce a grouping type (or use a tuple or pair) to associate the original sales region with the results of the query.

Answer 2

Hi @Cloud Developer , Welcome to Microsoft Q&A,

You need to parse the string yourself and perform the query logic. You can try the logic below.

public async Task<ActionResult> GetReportConditions(String region)
{
     var output = await _db.GetSalesRegions
         .Where(x => x.region == region)
         .Select(x => new
         {
             salesData = x.SalesReportFilters.Contains("MM") ? ParseString(x.SalesReportFilters) : await ExecuteQueryAsync(x.SalesReportFilters)
         })
         .ToArrayAsync();

     return new JsonResult(output);
}

public string ParseString(string filter)
{
     // Parse the string and return the result
     // This assumes you have already implemented the parsing logic
     return "parsed data"; // Return the parsed data here
}

public async Task<string> ExecuteQueryAsync(string query)
{
     //Execute the query and return the results
     // This assumes that you have implemented the logic to execute the query
     return "query result"; // Return the query result here
}

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.