How to create an Web API with LINQ in Blazor WASM

Question

Hi,
I am trying to implement Linq in API controller without success, to do simple calculations on my database.
In database there is a colunm RAF.
The controller should make :
_ to filter out negative values with the variable data
_ to count the total number of values in the database with the variable NumberECR;
_ to calculate the ratio with the variable Ratio

I've a message :

An unhandled exception has occurred while executing the request.
System.InvalidOperationException: The expression 'number.RAF' is invalid inside an 'Include' operation,

I didn't quite understand the use of linq.
Could you please give me some advise?

I defined a component Indicateur.razor.cs as follow :

    namespace WebApp3.Pages.Dashboard
    {
        public partial class IndicateurComponent
            {
             [Inject]
             HttpClient Http { get; set; }
                 public class Stats
            {
                public int calcul { get; set; }
                public int NombreECR { get; set; }
                public double Ratio { get; set; }
             }
             public async Task<Stats> MonthlyStats()
               {
                var response = await Http.SendAsync(new HttpRequestMessage(HttpMethod.Get, new Uri($"{UriHelper.BaseUri}api/servermethods/MonthlyStats")));
               //  string jsonStr = await response.Content.ReadAsStringAsync();
               //  Stats re = new Stats {NombreECR = int.Parse(jsonStr)};
               //  return re;
                  return await response.ReadAsync<Stats>();
                }
            }
        }

I defined a controller as follow :

namespace WebApp3.Server.Controllers
{
    [Route("api/[controller]/[action]")]
    public class ServerMethodsController : Controller
    {
         private readonly ApplicationDBContext context;

         public ServerMethodsController(ApplicationDBContext context)
        {
            this.context = context;
        }
        public IActionResult MonthlyStats()
        {                     
            double data = context.Developers
                           .Include(number => number.RAF)
                           .Where(number => number.RAF <0 )
                           .Count();

             var numberecr = context.Developers.Count();   
             var calcul = data / numberecr;

             var stats = context.Developers
                            .Where(number => number.RAF< 0)
                            .ToList()
                            .Select(group => new 
                            {                               
                             NombreECR = numberecr,
                             Ratio = calcul/numberecr,
                             });

             return Ok(stats);
        }
    }

}

Answer 1

Hi,

RAF is number that is calculed in Developer.cs

you will find below Developer.cs in share project.

namespace WebApp3.Shared.Models
{

    public partial class Developer
    {
        public int Id { get; set; }
  ….
        public DateTime DateSoldePr { get; set; }
        public DateTime? DateSuivi { get; set; }
        public int RAF { 
             get
                    {
                        if(DateSoldePr.Year < 2000)
                        {
                            return 0;
                        }
                        if(DateSoldePr.Year > 2000 )
                        {
                         return (int) Math.Floor((DateTime.Today - DateSoldePr).TotalDays);
                        }
                        else
                        {
                            return 0;
                        }
                    }  
         }
        …       
    } 
}

After the migrations in ApplicationsDBContextModelSnapshots.cs I didn't see RAF variable

namespace WebApp3.Server.Migrations
{
    [DbContext(typeof(ApplicationDBContext))]
    partial class ApplicationDBContextModelSnapshot : ModelSnapshot
    {
        protected override void BuildModel(ModelBuilder modelBuilder)
        {
#pragma warning disable 612, 618
            modelBuilder
                .UseIdentityColumns()
                .HasAnnotation("Relational:MaxIdentifierLength", 128)
                .HasAnnotation("ProductVersion", "5.0.6");

            modelBuilder.Entity("WebApp3.Shared.Models.Developer", b =>
                {
                    b.Property<int>("Id")
                        .ValueGeneratedOnAdd()
                        .HasColumnType("int")
                        .UseIdentityColumn();
            …

                    b.Property<DateTime>("DateSoldePr")
                        .HasColumnType("datetime2");

                    b.Property<DateTime?>("DateSuivi")
                        .HasColumnType("datetime2");

…
                });
#pragma warning restore 612, 618
        }
    }
}

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (24ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [d].[Id], …[d].[DateSoldePr], [d].[DateSuivi], …
FROM [Developers] AS [d]

We didn't see RAF column.
I suppose that it's not a good idea to put this calculation in Developer.cs

I propose to to you github link here : WebApp3B

Answer 2

Hi,
I added [NotMapped] at the RAF and there is always the same problem.
The solution should be to get out the RAF calculation from Developer.cs.
Could you have some advise to introduce the calculation of the RAF outside of developer.cs.
Thanks in advance

 {
                         if(DateSoldePr.Year < 2000)
                         {
                             return 0;
                         }
                         if(DateSoldePr.Year > 2000 )
                         {
                          return (int) Math.Floor((DateTime.Today - DateSoldePr).TotalDays);
                         }
                         else
                         {
                             return 0;
                         }
                     }