Entity Framework. C# To SQL Type Translation

When you declare a variable in Entity Framework model, migration translates the C# type to SQL Server type. Here is a table describing how each C# type is implemented in SQL.

C# primitive type C# type in System namespace SQL Server type
byte Byte tinyint
short Int16 smallint
int Int32 int
long Int64 bigint
 float Single real
double Double float
decimal Decimal decimal(18,2)
bool Boolean bit
DateTime datetime
char Char
string String nvarchar(max) null

Just two interesting things:

  1. char type is not translated to any SQL type;
  2. string type is the only type that will be nullable, the others are not.

And some tips:

  1. to make a field NULL, for example int, you should use a nullable type int? instead;
  2. to make a string field NOT NULL, place a [Required] attribute before declaration in EF model;
  3. if your string field won’t have an infinite length, you can (and I recommend to) place a [StringLength(n)] attribute where n can be up to 4000.

[UPDATE 2016-11-17]
Unsigned integer types and sbyte are not translated too. See here Not All .NET Types Can Be Used In Entity Framework

Turning On .NET Framework 3.5 On Windows 10

After some unsuccessful attempts to turn on .NET Framework 3.5 on a newly installed Windows 10 (even if I have a stable Internet connection) and a failed try to run .NET Framework installer, I found a very graceful way to do this.

1. Open Command prompt
2. Run this command

If everything goes smoothly, you’ll see these lines showing the happy end.

Deployment Image Servicing and Management tool
Version: 10.0.10240.16384

Image Version: 10.0.10240.16384

Enabling feature(s)
[==========================100.0%==========================]
The operation completed successfully.

Code First. Splitting Entity Into Two Tables

Entity Framework allows to divide a single entity to 2 separate physical tables. To do this you need to declare an entity as usual, and add a Fluent API command to map this entity to tables.

Declaration of entity:

Table annotation is not necessary here and will be omitted by Visual Studio.

Entity-table mapping:

After migration Entity Framework will create 2 tables with the names defined in OnModelCreating function.

In Entity Framework you work with a single entity, i.e. you can create/update entity as usual, but when you save changes, the database context will generate separate SQL commands to modify the corresponding tables (first, second or both).

Be aware if you skip a property in mapping, Entity Framework will create a 3rd table with key field and a new created one.

Code First. Executing Stored Procedures

Entity Framework can execute stored procedures in SQL Server. For example, I have a simple stored procedure as following:

In Code First model this can be done by different ways depending on whether you want to get a resultset from a stored procedure.

1. SqlQuery (returning a resultset)

or

If you need to pass a parameters, you need to add a parameter name after the name of stored procedure and supply SqlParameter. The command will be like this:

[UPDATE 2016-08-24]
SqlQuery returns a collection of type System.Data.Entity.Infrastructure.DbRawSqlQuery.

2. ExecuteSqlCommand (no resultset)

If you want to execute a stored procedure or an SQL command that won’t return a result set, you can call this function.

3. Low-level methods through ObjectContext

There’s an excellent article on MSDN Stored Procedures with Multiple Result Sets that describes how to get a couple of resultsets with the help of Database.Connection.CreateCommand and ObjectContext.Translate.

C#. Passing Lambda Expression As Parameter

Using delegates you can pass a lambda expression as a parameter to a function, and then use it in LINQ queries. This can be done with Func<…> delegates.

If you want to pass a lambda expression to be used, for example, in Where clause, you need a Func<T, bool> delegate. Just hover the mouse over Where in your code, and IntelliSense would show you the required syntax.

Here is a simple demo program:

C#. Passing Function As Parameter Using Delegate

I need a generic C# module that will execute different functions for some classes, or execute none. This can be done with delegates.

The basic info is described in MSDN articles:
Using Delegates (C# Programming Guide)
Func<T, TResult> Delegate

Here is my simple console application that would call different functions that are passed as parameters.

Querying Entity Model. Part 7 – Left Join With First Row

Let’s imagine the situation when a person could have a few phone numbers, for example, home, work, mobile for home country, mobile for traveling, etc. I want to make a list of clients with just one phone number. This phone number can be chosen arbitrary or the first from an ordered list. This task can be done in Entity Framework Code First model by using a LINQ query.

I have Clients and Phones entities, and I’ll create a query in lambda syntax to select client names with a phone number with a first country code. I’ve found three ways to do this.

1. Join with first row using subquery

2. Join with first row using Navigation properties

3. Join with first row using GroupJoin

Entity Framework generates the same T-SQL query in all these cases.

From the SQL Server perspective, using APPLY operator is not a good solution. APPLY executes a subquery for each client row. You can read more about APPLY operator in MSDN article Using APPLY.

The most efficient way for SQL Server will be a query with Window function such as ROW_NUMBER().

To check the efficiency you need to insert a lot of records to your database, copy both scripts to SQL Server Management Studio and look at the Estimated or Actual Execution Plan. In a test with 4 client rows I have 74% and 26% relative to a batch. The percentages of the query cost could differ due to number of rows/data pages/clustered indexes to be processed, but the result is the query with APPLY is much heavier than that with a ROW_NUMBER() function.

I think you should avoid queries with APPLY to get many records. In these cases it would better to invoke a T-SQL queries or stored procedures from Entity Framework.