ORM/LINQ/SQL syntax Flashcards
What is ORM?
Object Relational Mapper
what do ORMs do?
ORMs maps data from DB to an object in your program
what are the two ways to connect your database to your code?
DB first and Code first
what is DB first?
The DB structure already exists, classes are created based on the existing structure
what is code first?
You create the DB structure based on the classes in the code
A _____ instance represents a session with the database and can be used to query and save instances of your entities
DBContext
Used to connect to your db
Connection String
The project that you build and run
Startup project
what is the syntax to create a migrations folder
dotnet ef migrations add -c –startup-project
a snapshot of the database schema given the current state of your models
Migration
what is the syntax to apply the changes of the latest migration to your database
dotnet ef database update
What does LINQ stand for?
Language-Integrated Query
a uniform query syntax in C# and VB.NET to retrieve data from different sources and formats. It is integrated in C# or VB, thereby eliminating the mismatch between programming languages and databases, as well as providing a single querying interface for different types of data sources.
LINQ
3 parts of a query operation:
Data Source
Query
Execution
Expressions that retrieve data from a data source
LINQ Queries
a set of instructions that describes what data to retrieve from a given data source (or sources) and what type and organization the returned data should have.
query
query expressed in query syntax
query expression
what clause starts a query expression
from
what clause ends a query expression
select or group
how many clauses can be in a query expression
one or more
what are some clauses?
where, orderby, join, let
what does the INTO keyword in a query expression do?
enable the result of a join or group clause to serve as the source for additional query clauses in the same query expression
From an application’s viewpoint, the specific type and structure of the original source data is not important
True
The application always sees the source data as an IEnumerable or IQueryable collection.
True
A query can:
1. Retrieve a subset of the elements to produce a new sequence without modifying the individual elements. The query may then sort or group the returned sequence in various ways
2. Retrieve a sequence of elements but transform them to a new type of object.
3. Retrieve a singleton value about the source data, such as:
The number of elements that match a certain condition.
The element that has the greatest or least value.
The first element that matches a condition, or the sum of values in a specified set of elements.
True
any variable that stores a query instead of the result of a query.
query variable
A query variable is always an enumerable type that will produce a sequence of elements when it is iterated over in a foreach statement or a direct call to its IEnumerator.MoveNext method.
True
By default, linq queries have deferred execution. This means that unless iterated via a foreach loop, the query isn’t going to retrieve data from the data source.
True
You can force immediate execution by either using an aggregate function in the query such as count or by calling the ToList() or ToArray() methods.
True
what are the ways to query using LINQ?
Query syntax and method syntax
Used for retrieving data from the database
SELECT Statement
The SELECT statement has several clauses:
Select From Where Group by Having Order by
What is the logical order of operations to the SELECT statement
from, where, group by, having, select, order by
Used to unnormalize 2 or more tables into one table, on a common column
JOIN
What is an example syntax using JOIN?
SELECT * FROM Table_1 JOIN Table 2 ON Table_1.Id = Table_2.Table1Id
what are the join types?
Inner (Default)
Full
Left
Right
Combines rows from both the queries
UNION
Keeps only those rows which are common in both queries
INTERSECT
Keeps rows from the left query which are not included in the right query
MINUS
