Querying Entity Model. Part 20 – Retrieving Some Records From Groups

Entity Framework allows to retrieve records from groups in any quantity and from any position, i.e. first record, records 1…N, second record, or records 5…10. These can be made with LINQ methods like First(), Take() and Skip().

When I write T-SQL code I often use ROW_NUMBER() function to generate serial numbers for groups of records, then filtering the required serial numbers of rows. I suspect that EF would generate a less efficient code with APPLY operator (look at this post Querying Entity Model. Part 7 – Left Join With First Row). Now it’s time to confirm this.

Retrieving first row

Another way is to use Take() method.

Entity Framework uses SELECT DISTINCT to get group key values and APPLY operator to get a record for each group. EF uses OUTER APPLY in the first case, and CROSS APPLY in the second.

Retrieving first N rows

The only difference in T-SQL code is the line CROSS APPLY (SELECT TOP (2).

Retrieving rows with offset

I try to get exactly the second rows from groups. Skip() method needs a sorted list, so I have to place OrderBy() before it.

EF generates T-SQL code with OFFSET .. FETCH for SQL Server 2016.

Querying Entity Model. Part 19 – Full Outer Join

There is a rarely used join in SQL as full outer join. It combines the intersection between two sets, and both set exception from left and right parts of the join. Full outer join might be used to merge data from source and target tables, so you would get intersecting rows to update, new rows to insert, and missing rows to delete.

This type of join also can be implemented by LINQ queries on Entity Framework in four steps:

  1. Left outer join
  2. Right outer join
  3. Concat both outer joins
  4. Get distinct result

Here is an abridged T-SQL code (I’ve deleted extra lines in SELECT clause for readability).

As you see, T-SQL code repeats the logic of LINQ. Comparison of execution plans shows that a native FULL OUTER JOIN has cost 36% against 64% of LINQ in my case. Maybe you can get a bigger difference.

And native T-SQL code is more elegant 🙂

Useful NLog Targets

NLog has many targets to which it can save log entries. Here are some that I consider quite useful.

File

I’ve added some parameters to archive log on everyday basis.

EventLog

NLog will create custom application log MyAppLog.

Visual Studio Debug window

Also NLog allows to save log in a database table, but it needs more efforts and lines of code to setup it. You can read more in the article Database target in NLog project documentation.

Reloading Configuration In NLog

When you use NLog in your C# project, its configuration is loaded once on the application startup. Now I want to change the configuration (targets, rules, levels) while application is running. It’s easy – just edit NLog.config file.

That’s not enough. Application should notice that changes and reapply them. This can be triggered by re-reading NLog.config again at some point, by pressing the button or calling Web API, i.e. you or your application should initiate reloading.

As for NLog, reloading is implemented by 3 lines of code.

Debugging SQL Server CLR Function In Visual Studio 2015

This is a step-by-step instruction of how to debug CLR code in Visual Studio. It is based on MSDN article Debugging CLR Database Objects. As stated in the comment there, the process can differ for different versions of Visual Studio. I’ve got Visual Studio 2015 Community Edition with Update 3.

1) Run Visual Studio with Administrator rights. It’s needed for VS to attach the SQL Server process.
If you skip this, you might see an error later:
Unable to debug .NET code. Could not attach to SQL Server process on 'VS2015'. Operation not supported. Unknown error: 0x80004005.
2) Open SQL Server Object Explorer in Visual Studio.
3) Right-click server and select Allow SQL/CLR Debugging.
4) Drill down to CLR function, right-click and select Debug Function…
5) In the Debug Function window enter value for input parameter and press OK.
6) Visual Studio will generate a test script and enter into the debug mode.
7) Now you can press F11 to step into the test script code and CLR function.

During the last step Windows Firewall could ask you to allow VS debugger to connect through network. Please allow it.
clr-debugging-firewall

Also Visual Studio will always ask you to attach to SQL Server process in order to debug CLR code. Press Attach here.
clr-debugging-attach-sql-server-process

Creating SQL Server CLR Function In Visual Studio 2015

If you try to find an example how to create CLR code for SQL Server, you may encounter MSDN article How to: Create and Run a SQL Server User-Defined Function by using Common Language Run-time Integration, but it describes the process for Visual Studio 2010. Now I’ll show how to do it in VS 2015.

1) Create or open SQL Server Database Project.
2) Add new item and choose SQL CLR C# User Defined Function type.
3) Write the code like in this primitive case.

It’s important to use correct types for SQL Server and C# – SqlDouble and double respectively.
4) Publish the project.

Although the project was deployed successfully, you need to enable CLR on SQL Server. Run the following script:

If you won’t do that, you’ll get an error when you try to run this CLR function:

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.