Table created in database after using NotMapped

[Manoj-Talukdar]

I attempted to implement a ViewModel in my project, but encountered an issue. When I used the "NotMapped" attribute in my DbContext's DbSet for the ViewModel, a table for the ViewModel was unexpectedly created in the database. On the other hand, when I placed the "NotMapped" attribute in the Model, the table was not created, but it resulted in an error.

I also tried to use the Database.SqlQuery method, but I received an error indicating that this method is not supported by the database provider. How can I execute a raw SQL query without generating a table in the database? How can I run SqlQuery Method with Pomelo

MySQL version: 8 (Azure database for mysql) Operating system: Pomelo.EntityFrameworkCore.MySql version: 7.0.0 Microsoft.AspNetCore.App version: 6

[lauxjpn]

When I used the "NotMapped" attribute in my DbContext's DbSet for the ViewModel, a table for the ViewModel was unexpectedly created in the database. On the other hand, when I placed the "NotMapped" attribute in the Model, the table was not created, but it resulted in an error.

@Manoj-Talukdar Please post a model (model classes and related Fluent API calls) and instructions, so we can reproduce the issues on our side.

Please also post the error/exceptions that you are getting.

---

I also tried to use the Database.SqlQuery method, but I received an error indicating that this method is not supported by the database provider. How can I execute a raw SQL query without generating a table in the database? How can I run SqlQuery Method with Pomelo

Using DbContext.Database.SqlQuery<TResult>() and DbContext.Database.SqlQueryRaw<TResult>() is both supported in Pomelo 7.0+.

Using EF Core 7.0+, those methods can return a scalar value (that must be named Value). Using EF Core 8.0+, those methods can also return an unmapped type (similar to an entity type, but unknown to the model).

Here is a simple console program to demonstrate these concepts:

Program.cs

using System;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate;

// A regular entity type mapped in the model.
public class IceCream
{
    public int IceCreamId { get; set; }
    public string Name { get; set; }
    public bool IsFavorite { get; set; }
    public int BatchNumber { get; set; }
}

// Not mapped in the model.
public class PromotionalIceCream
{
    public int IceCreamId { get; set; }
    public string Name { get; set; }
}

public class Context : DbContext
{
    public DbSet<IceCream> IceCreams { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=Issue1799";
            var serverVersion = ServerVersion.AutoDetect(connectionString);

            optionsBuilder
                .UseMySql(connectionString, serverVersion)
                .LogTo(Console.WriteLine, LogLevel.Information)
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<IceCream>(
            entity =>
            {
                entity.HasData(
                    new IceCream { IceCreamId = 1, Name = "Vanilla", IsFavorite = true, BatchNumber = 1 },
                    new IceCream { IceCreamId = 2, Name = "Chocolate", IsFavorite = true, BatchNumber = 42 },
                    new IceCream { IceCreamId = 3, Name = "Matcha", IsFavorite = false, BatchNumber = 42 });
            });
    }
}

internal static class Program
{
    private static void Main()
    {
        using var context = new Context();
        
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();
        
        // Uses `SqlQuery` to return a **scalar** value that must be named `Value`.
        var favoriteIceCreamsCount = context.Database
            .SqlQuery<int>(
                $"""
SELECT COUNT(*) as `Value` FROM `IceCreams`
WHERE `IsFavorite` = 1
""")
            .Single();
        
        Trace.Assert(favoriteIceCreamsCount == 2);

        // The type PromotionalIceCream is not mapped in the model.
        // This does only work in EF Core 8.0+.
        var promotionalIceCreams = context.Database
            .SqlQuery<PromotionalIceCream>(
                $"""
SELECT `IceCreamId`, `Name` FROM `IceCreams`
WHERE `BatchNumber` = 42
""")
            .OrderBy(i => i.IceCreamId)
            .ToList();
        
        Trace.Assert(promotionalIceCreams.Count == 2);
        Trace.Assert(promotionalIceCreams[0].Name == "Chocolate");
        Trace.Assert(promotionalIceCreams[1].Name == "Matcha");
    }
}

Output (SQL)

warn: 07.03.2024 01:58:42.474 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure) 
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled 
during development.
info: 07.03.2024 01:58:43.180 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (35ms) [Parameters=[], CommandType='Text', CommandTimeout='30']                                
      CREATE DATABASE `Issue1799`;                                                                                      
info: 07.03.2024 01:58:43.425 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      ALTER DATABASE CHARACTER SET utf8mb4;
info: 07.03.2024 01:58:43.467 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (41ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `IceCreams` (
          `IceCreamId` int NOT NULL AUTO_INCREMENT,
          `Name` longtext CHARACTER SET utf8mb4 NULL,
          `IsFavorite` tinyint(1) NOT NULL,
          `BatchNumber` int NOT NULL,
          CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
      ) CHARACTER SET=utf8mb4;
info: 07.03.2024 01:58:43.483 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (15ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      INSERT INTO `IceCreams` (`IceCreamId`, `BatchNumber`, `IsFavorite`, `Name`)
      VALUES (1, 1, TRUE, 'Vanilla'),
      (2, 42, TRUE, 'Chocolate'),
      (3, 42, FALSE, 'Matcha');
info: 07.03.2024 01:58:43.745 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `t`.`Value`
      FROM (
          SELECT COUNT(*) as `Value` FROM `IceCreams`
          WHERE `IsFavorite` = 1
      ) AS `t`
      LIMIT 2
info: 07.03.2024 01:58:47.379 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `i`.`IceCreamId`, `i`.`Name`
      FROM (
          SELECT `IceCreamId`, `Name` FROM `IceCreams`
          WHERE `BatchNumber` = 42
      ) AS `i`
      ORDER BY `i`.`IceCreamId`

(The CREATE TABLE statement is generated by the context.Database.EnsureCreated() call in the sample code, not the SqlQuery() calls.)

For more information about SqlQuery, see

• SQL Queries
• SQL Queries: Querying scalar (non-entity) types

[lauxjpn]

When I used the "NotMapped" attribute in my DbContext's DbSet for the ViewModel, a table for the ViewModel was unexpectedly created in the database. On the other hand, when I placed the "NotMapped" attribute in the Model, the table was not created, but it resulted in an error.

@Manoj-Talukdar Please post a model (model classes and related Fluent API calls) and instructions, so we can reproduce the issues on our side.

Please also post the error/exceptions that you are getting.

@Manoj-Talukdar Please provide the requested information, or we have to close this issue. Thanks!