CODEWITHSUNDEEP
c#sqlentity-frameworkentity-framework-core
crystyxn
crystyxn

Reputation: 1601

Querying a view in SQL through Entity Framework Core

I am trying to query an already existing view in SQL Server which I am connected to in Visual Studio 2019.

I've created a class corresponding to the name of my Users database called UsersDbContext.:

 class UsersDbContext : DbContext
    {            
        public DbSet<EventView> Users { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Data Source=REDACTED;User ID=REDACTED;Password=REDACTERD;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
        }
    }

In this database I have a view named eventview.fss. How do I access the data from here from Entity Framework?

In my main class where I want to process the data:

private static readonly UsersDbContext _context = new UsersDbContext();

var myid = _context.Users.FromSqlRaw($"SELECT Id FROM eventview.fss");

In my models class I've added a model for the view. It is very simple:

public class EventView
{
    public string Date { get; set; }
    public string Id { get; set; }
}

What am I missing? Ideally I'd like to have the the rows from the view be put into a List<EventView> then I can work with the data from there.

Edit: when I try to run _context.Users.FromSqlRaw($"SELECT Id FROM eventview.fss").ToList(); I get the error for SqlException: Invalid object name eventview.fss

Upvotes: 0

Views: 2132

Answers (2)

pjs
pjs

Reputation: 383

You can tell EF Core how to retrieve the data set so you don't need to use raw sql. Add a dbset in the context. In OnModelCreating, something like: builder.Entity().HasNoKey().ToView("eventview.fss", "YourSchemaName");

Upvotes: 2

crystyxn
crystyxn

Reputation: 1601

My problem was that my connection string was missing the Database property. Once I've added it now I can pull the data.

Upvotes: 0

Related Questions