Querying a view in SQL through Entity Framework Core

Question

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

pjs · Accepted Answer · 2021-03-14 16:06:52Z

2

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");

answered Mar 14, 2021 at 16:06

pjs

3832 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

crystyxn · Accepted Answer · 2021-03-11 16:42:25Z

0

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

answered Mar 11, 2021 at 16:42

crystyxn

1,6337 gold badges38 silver badges75 bronze badges

Collectives™ on Stack Overflow

Querying a view in SQL through Entity Framework Core

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related