Dbquery vs dbset

By Christos S. DbContext class is the one among others that the Entity Framework team has created, in order to facilitate developers when accessing and manipulating database data.

In this post we will show how to use the DbContext class in order to retrieve data from database. This Domain Model project has at the moment the following two classes, Professor and Course.

dbquery vs dbset

Each professor can be assigned with many courses while each course can be assigned in only one professor One-To-Many relationship. The relationship between these two classes will be required. Continue by adding a new class library project named DataAccess. I have added a configuration class file named ModelConfigurations.

Next, create a new class file named UnivercityContext and paste the following code, in order to let Entity Framework know about our classes. Finally, I have created a new class file in the DataAccess project again, to ensure that each time you run the application the database will be re-created and seed with some mock data.

Add a new console application project named UnivercitySecretaryadd to it references both for the Model and the DataAccess projects and finally make sure you install the Entity Framework too.

Modify the Program. Set the console application as the start up project, build and run your application. All professors should be displayed in the console. Mind thought, that in the way DisplayAllProfessors is coded at the moment, professor records are retrieved from database on demand when needed while iterating, which means a different SQL query is sent to the database for each professor.

To retrieve all professors at once, change slightly the DisplayAllProfessors as follow. Using ToList function, will cause to fill professors with all professor records at once.

Sorting retrieved entities is quite easy using LINQ. Add the following function and call it in your main method to get all professors ordered by name. Again, each result is retrieved as needed from the application. You could replace the query code to get all professors ordered by name at once, as follow.

In the previous examples, we retrieved professor objects but all we wanted was to display their names.The DbSet class represents an entity set that can be used for create, read, update, and delete operations.

Keyless Entity Types

The context class derived from DbContext must include the DbSet type properties for the entities which map to database tables and views. When the changes are saved, the entities in the Added states are inserted into the database. After the changes are saved, the object state changes to Unchanged. Example: dbcontext.

Inherited from DbQuery. This will be a significant performance boost for read-only entities. Attach studentEntity ; Create Entity Creates a new instance of an entity for the type of this set. This instance is not added or attached to the set. The instance returned will be a proxy if the underlying context is configured to create proxies and the entity type meets the requirements for creating a proxy.

Create ; Find int Entity type Uses the primary key value to find an entity tracked by the context. If the entity is not in the context, then a query will be executed and evaluated against the data in the data source, and null is returned if the entity is not found in the context or in the data source.

Note that the Find also returns entities that have been added to the context but have not yet been saved to the database. Include "StudentAddress". When the changes are saved, the entity is deleted from the database.

The entity must exist in the context in some other state before this method is called. Entity Framework Courses on Pluralsight. Adds the given entity to the context with the Added state. Add studentEntity. Returns a new query where the entities returned will not be cached in the DbContext. Attaches the given entity to the context in the Unchanged state Example: dbcontext. Attach studentEntity. Creates a new instance of an entity for the type of this set. Create. Uses the primary key value to find an entity tracked by the context.

Find 1. Marks the given entity as Deleted. Remove studentEntity. Creates a raw SQL query that will return entities in this set.Because we could optimize that process and share our knowledge with other folks out there. As you probably guess the best way is to simply… write a blog post! A week ago I was asked about using Include method while using Entity Framework? Is that related to lazy loading? Should we use it every time when working with IQueryable?

Before we move forward to the Include method, let me introduce a simple EF example with two entities one-to-many relation and their Database context. The code below presents the following:. The idea that stands behind is really simple. Now the whole process is nothing more than making round trips to the database for data if needed of course in runtime. What does it mean? As wrote before, in this case, EF will do a second query to the DB for the books of the specific author and all magic will happen in runtime.

The listings below presents the queries generated by EF starting with the Author query:. Lazy loading is a great mechanism but only if you know when and how to use it.

But look at our example again. Did we really have to fetch missing data in two parts? Of course not, since the code that needed books is placed right after the first query.

The listing below presents the generated query:. If you have never heard about it, stop reading this article for a moment and read this one. Entity Framework also supports another type of fetching data — eager loading.

In order to do that, we need to remove all virtual properties and we should disable the option in the context object. What happened? We informed EF that the only entity we need is Author which meant the books collection was equal to null just like in the first example. You forgot something? You have to deal with NullReferenceException! How can we deal with that? Generated SQL is also identical to the previous. Frankly, I would say that the following solution should be your default way of dealing with IQuerable.

This one tells EF exactly what we need, so it does not need yet another help as Include method. As I wrote, I strongly recommend using this approach. Share the post "When to use Include with Entity Framework?

NET Core. When to use Include with Entity Framework? FirstOrDefault ; foreach var book in author. Books Console.

WriteLine book. FirstOrDefault ; foreach var title in author.

When to use Include() with Entity Framework?

BookTitles Console. Documenting ASP. Popularne wpisy.So EntityFramework 6 is a lot better testable then previous versions. And there are some nice examples on the internet for frameworks like Moq, but the case is, I prefer using NSubstitute. I've got the "non-query" examples translated to work with the use of NSubstitute, but I can't get my head around the 'query-test'.

How does Moq's items. Returns data. Provider ; translate to NSubstitute? Provider ; but that didn't work. I've also tried items. Provider ; but that didn't work either. So let me quote the code example from the link above. NSubstitute calls the Provider's getter, then it specifies the return value. This getter call isn't intercepted by the substitute and you get an exception. It happens because of explicit implementation of IQueryable.

Provider property in DbQuery class. You can explicitly create substitutes for multiple interfaces with NSub, and it creates a proxy which covers all specified interfaces. Then calls to the interfaces will be intercepted by the substitute. Please use the following syntax:. The unittest code samples are mocking DbSetbut NSubstitute requires the interface implementation.

You're not always required to provide the Interface, so that's why I was confused. But in this specific case, it turned out to be crucial. Question So EntityFramework 6 is a lot better testable then previous versions. The exeption I'm getting is: "System.In the last tutorial, we looked at the DbContext class in detail.

In this tutorial let us look at the DbSet and the Methods provided by it to work with the entities. A DbSet represents an entity set. An entity set is defined as a set of entities of the same entity type. This allows us to query the database using the LINQ query. DbSet inherits from DbQuery and exposes the query capabilities.

This method Adds the given entity to the context in the Added State. These entities is inserted into the database table when the saveChanges on the context is called. This method adds the collection of entities to the context. All the entities in the collection are marked as Added State. All the entities are inserted into the database table when saved by the context by Calling SaveChanges method Note that entities that are already in the context in some other state will have their state set to Added.

dbquery vs dbset

Attach method attaches the given entity to the context. State of the attached will be marked as Unchanged. It means calling savechanges on the context will have no effect on the database. It is an extension method where the returned entities are not be tracked by the context.

This method creates and returns a new instance of an entity. This entity is not added or attached to set. Finds and returns an entity with the given primary key values. Context always looks for the entity in the context first and returns it if found.

The entity if not found then the null is returned. The method used to delete the entity from the database. The entity is not deleted immediately. They are marked as deleted. The entity is Deleted from the database when SaveChanges is called.

The method used to delete the collection of entities.

dbquery vs dbset

It does so by changing the state of entities to the deleted state. When the SaveChanges method is invoked, Context will delete these entities from the database. This method uses the raw SQL query to return the entities. It has two parameters. The second is the parameters to apply to the SQL query. This site uses Akismet to reduce spam.

Learn how your comment data is processed. Configuring Models in EF. Leave a Reply Cancel reply Your email address will not be published. Comment Name Email. Our web site uses cookies to ensure that we give you the best experience on our website. If you continue to use this site we will assume that you are happy with it. Ok Read more.A DbSet represents the collection of all entities in the context, or that can be queried from the database, of a given type.

Set method. Methods and properties that will be used by the test double must be implemented by the test double except AsNoTracking, AsStreaming, an Include where the default implementation is a no-op. This local view will stay in sync as entities are added or removed from the context. Likewise, entities added to or removed from the local view will automatically be added to or removed from the context. Gets a String representation of the underlying query.

Adds the given entity to the context underlying the set in the Added state such that it will be inserted into the database when SaveChanges is called. Adds the given collection of entities into context underlying the set with each entity being put into the Added state such that it will be inserted into the database when SaveChanges is called. Returns a new query where the entities returned will not be cached in the DbContext.

Attaches the given entity to the context underlying the set.

DBSet in Entity Framework

That is, the entity is placed into the context in the Unchanged state, just as if it had been read from the database. Creates a new instance of an entity for the type of this set. Note that this instance is NOT added or attached to the set. The instance returned will be a proxy if the underlying context is configured to create proxies and the entity type meets the requirements for creating a proxy. Creates a new instance of an entity for the type of this set or for a type derived from the type of this set.

Finds an entity with the given primary key values. If an entity with the given primary key values exists in the context, then it is returned immediately without making a request to the store.

Otherwise, a request is made to the store for an entity with the given primary key values and this entity, if found, is attached to the context and returned.

If no entity is found in the context or the store, then null is returned. Asynchronously finds an entity with the given primary key values.

Marks the given entity as Deleted such that it will be deleted from the database when SaveChanges is called. Note that the entity must exist in the context in some other state before this method is called. Removes the given collection of entities from the context underlying the set with each entity being put into the Deleted state such that it will be deleted from the database when SaveChanges is called.

Creates a raw SQL query that will return entities in this set. Note that the entities returned are always of the type for this set and never of a derived type. If the table or tables queried may contain data for other entity types, then the SQL query must be written appropriately to ensure that only entities of the correct type are returned.GitHub is home to over 40 million developers working together to host and review code, manage projects, and build software together.

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community. Already on GitHub? Sign in to your account. Im a newbie for entity framework core and ngpsql. I use it to connect Amazon Redshift database. I want to execute raw sql. How can i execute raw queries?

Any idea? The general EF Core docs, which you should go through, are here. If you want to use raw SQL to query entities which are mapped in your context, read this. Thank you for the quick response, I checked the docs you mentioned but i cant see anything that i didnt know. I don't know am i missing a point here?

The doc about raw sql already recommends to use FromSql method but my problem is that method doesn't exists on my DBSet. On the other hand i checked ctx. GetDbConnection that doesnt exist either. Thanks again. Im sorry: Microsoft. EntityFrameworkCore namespace was missing in the class where i used my datacontext. This helped to fix my issue. While the Microsoft.

Relational Package. Skip to content. Dismiss Join GitHub today GitHub is home to over 40 million developers working together to host and review code, manage projects, and build software together. Sign up. New issue. Jump to bottom. Copy link Quote reply.


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *