SQL Server - dotnet falcon

Awesome Northwind

Feb 5, 2019

Whenever I give a talk at a meetup or teach at a course or just want to try a demo about a data driven technology, my biggest problem (and the biggest nag) is always to get some real(istic) sample data. Thankfully there are sample databases available on the internet

StackOverflow adventures: Don't create non-clustered index for foreign keys with Entity Framework Code First

Akos Nagy

Sep 20, 2017

I found an interesting question on stackoverflow: https://stackoverflow.com/questions/46212828/ef6-preventing-not-to-create-index-on-foreign-key OP wanted to find a way to disable index creation for foreign keys. I don't believe there is an easy way to do this; my mind turned right to custom SQL generators and scaffolders. Using custom SQL

Restoring from BACPAC times out in SSMS

Akos Nagy

Sep 11, 2017

When moving databases from the cloud to on-premise (usually for some advanced 'hacking', performance-tuning or just experimenting), I often use the BACPAC format. It is basically a zip file that encapsulates the schema and the contents of a database. I can export it with SSMS, and then re-import it the

SQL Server

Entity Framework Logical Delete Part Seven: Filtering deleted records using Row-Level Security

Akos Nagy

Jul 31, 2017

So in the previous post, I gave some solutions that can be used for filtering the soft deleted entities. The EF Core solution is kinda neat, but the others' have at least one problem: you have to include the IsDeleted column in the model, which leads to abstraction leaking. How

Entity Framework Logical Delete Part Six: Filtering deleted records the naive way

Akos Nagy

Jul 29, 2017

OK, so we've got to the point where we have a good solution for flagging deleted entities. But we still have to find a way to filter out the flagged records from query results. Just as a reminder: we need a solution that works for entities, for related entities, has

Entity Framework Logical Delete Part Five: Cascade logical delete with cascade update

Akos Nagy

Jul 28, 2017

Microsoft SQL Server has a little-known and little-used feature called cascade update. Just like cascade delete, which deletes the referring records along a FK, the cascade update feature updates the values of the FK in the referring entities if the referred value changes. So let's say you have a table

Entity Framework Logical Delete Part Four: Automating using stored procedures

Akos Nagy

Jul 27, 2017

In the last post of the series, I identified a good enough solution to handle the EF soft delete scenario with good performance and no abstraction leaking. The solution itself contains a well-defined process, but ideally this process should be automatized as much as possible. Just a reminder about the

Entity Framework Logical Delete Part Three: Using stored procedures

Akos Nagy

Jul 25, 2017

In Part 1 of this series I discussed what logical delete is, in Part 2 I gave a solution that is kinda good for cases when you don't have cascading requirements. But in that post I pointed out the solutions inadequacy to handle the cascading situation. Here in Part 3,

Entity Framework Logical Delete Part Two: Flagging deleted Entities with a naive solution

Akos Nagy

Jul 18, 2017

So in my previous post in the series, I described what soft delete is and what challenges need to be overcome when implementing it using Entity Framework (since EF has no explicit support for this). Now, in part two, I want to discuss a naive solution — one that might satisfy

Entity Framework Logical Delete Part One: Intro and Questions

Akos Nagy

Jul 17, 2017

I've been planning to write a blog post about how to achieve the so called "logical delete" or "soft delete" when using Entity Framework. This is the kind of requirement you have when you are not allowed to physically delete records from your database when deleting (e.g. for auditing

Stealing time in SQL Server

Akos Nagy

Mar 20, 2017

Here's a question for you: what is the output of this simple script in T-SQL if I run it on SQL Server 2016 Developer Edition (or any other edition and any other version, for that matter): declare @t1 datetime='2017-03-20 12:46:37.000'; declare @t2 datetime='2017-03-20 12:46:

SQL Server