4

I have a query where I'm doing a Count on a specific join/column - If I run this code with the "Clicks" [NotMapped] attribute removed, all the values populate properly - but then inserts fail since "Clicks" is not a valid column name. When I mark the column as [NotMapped] then it's not populating from this statement. How can I use raw SQL and populate a [NotMapped] column?

Code:

var query = db.URLs.FromSqlRaw(
        @"SELECT [u].[Key], [u].[Url], COUNT(c.Id) AS [Clicks]
          FROM[URLs] AS[u]
          LEFT JOIN[Clicks] AS[c] ON[u].[Key] = [c].[ShortUrlKey]
          GROUP BY[u].[Key], [u].[Url]")
          .AsQueryable<ShortURL>();

            var urls = query.ToList();

Model (works for inserts, but doesn't populate Clicks property):

public class ShortURL
{
    public string Key { get; set; }
    public string Url { get; set; }

    [NotMapped()]
    public int Clicks { get; set; } // doesn't populate from raw query
}

Model (works for queries, but fails on inserts)

    public class ShortURL
{
   public string Key { get; set; }
   public string Url { get; set; }
   public int Clicks { get; set; } // not in DB
}
calebjenkins
  • 611
  • 5
  • 6

1 Answers1

1

This is not the best method but is an option you can use to solve your problem:

  1. Create a new DbSet in you DBContext list:

    public DbSet<YourMode> actual_table_name { get; set; }

    public DbSet<CopyWithUnmappedModel> arbitary_table_name { get; set; }

  2. Duplicate the entire YourModel under the YourModel and give it any name you want (CopyWithUnmappedModel)

  3. Remove all the NotMapped fields and place them in the arbitary model with the NotMapped attribute

  4. Use the arbitary_table_name instead of actual_table_name where you want to only Read the date. Since you are not writing to the table the name of the table does not mater. And use the the first one actual_table_name for writing.

user12282614
  • 49
  • 2