Edi Wang

From Azure Functions to a Database Outbox: Simplifying Moonglade Email Notifications

2026-08-03T23:38:30Z

Moonglade used to send email notifications through a standalone service named Moonglade.Email.

That service was deployed as an Azure Function. The blog application submitted email notification messages, Azure Storage Queue held those messages, and the function processed them asynchronously before sending email through Azure Communication Services or SMTP.

It worked. But for a personal blogging system, it was more infrastructure than I wanted to keep.

Moonglade usually sends very few notification emails: new comments, admin replies, Webmentions, and test messages. Even for a busy post, the workload is tiny compared with what Azure Queue Storage and Azure Functions are designed to handle.

So I moved email notification processing back into the main Moonglade application.

Before: A Separate Azure Function Service

The old design looked like this:

sequenceDiagram
    participant Blog as Moonglade Web App
    participant Api as Moonglade.Email HTTP Function
    participant Queue as Azure Storage Queue
    participant Function as Azure Function Queue Trigger
    participant Template as Email Template Renderer
    participant Provider as ACS / SMTP

    Blog->>Api: Send email request
    Api->>Queue: Enqueue email message
    Queue->>Function: Trigger with queued message
    Function->>Template: Render email template
    Template-->>Function: Return rendered email
    Function->>Provider: Send email

This architecture had a few benefits:

email sending did not block comment submission
queue messages survived restarts
retries were handled outside the web request
email delivery was separated from the blog process

But it also required:

another repository
another deployment unit
Azure Functions
Azure Storage Queue
extra secrets and configuration
more operational knowledge for a small personal blog

For Moonglade, the cost was no longer justified.

After: An In-App Database Outbox

The new design keeps asynchronous and durable delivery, but removes the standalone Azure Function and Azure Storage Queue.

flowchart TD
    Event["Comment / Reply / Webmention"] --> Queue["IEmailNotificationQueue"]
    Queue --> Table["EmailOutboxMessage Table"]
    Table --> Worker["EmailOutboxWorker"]
    Worker --> Processor["EmailOutboxMessageProcessor"]
    Processor --> Builder["MessageBuilder"]
    Builder --> Dispatcher["EmailDispatcher"]
    Dispatcher --> ACS["Azure Communication Services"]
    Dispatcher --> SMTP["SMTP"]

The database is now the durable queue.

This is intentionally simple. Moonglade already requires SQL Server or PostgreSQL, so using the existing database avoids introducing RabbitMQ, Redis, Azure Queue Storage, or another hosted dependency.

Code Structure

The email implementation now lives inside the main Moonglade solution:

src/
  Moonglade.Email/
    CommentEvent.cs
    CommentReplyEvent.cs
    MentionEvent.cs
    TestEmailEvent.cs
    ServiceCollectionExtensions.cs
    Core/
      IEmailNotificationQueue.cs
      IEmailOutboxStore.cs
      DbEmailNotificationQueue.cs
      EmailOutboxWorker.cs
      EmailOutboxMessageProcessor.cs
      EmailOutboxWorkerOptions.cs

  Moonglade.Data/
    Entities/
      EmailOutboxMessageEntity.cs
      EmailOutboxMessageStatus.cs
    Configurations/
      EmailOutboxMessageConfiguration.cs

The main idea is:

event handlers enqueue email work
the database stores pending messages
a hosted service polls and claims messages
the processor sends emails and records success, retry, or dead-letter status

Event Handlers Only Enqueue Work

Request handlers do not send email directly. They publish LiteBus events such as CommentEvent, CommentReplyEvent, or MentionEvent.

The email event handlers turn those events into outbox messages.

public class CommentNotificationEventHandler(
    IEmailNotificationQueue queue,
    IBlogConfig blogConfig) : IEventHandler<CommentEvent>
{
    public async Task HandleAsync(CommentEvent notification, CancellationToken ct)
    {
        if (!blogConfig.NotificationSettings.EnableEmailSending)
        {
            return;
        }

        var payload = new NewCommentPayload
        {
            Username = notification.Username,
            Email = notification.Email,
            IpAddress = notification.IPAddress,
            PostTitle = notification.PostTitle,
            CommentContent = ContentProcessor.MarkdownToCommentHtml(notification.CommentContent)
        };

        await queue.EnqueueAsync(new EmailNotification
        {
            MessageType = MessageTypes.NewCommentNotification,
            DistributionList = blogConfig.GeneralSettings.OwnerEmail,
            MessageBody = JsonSerializer.Serialize(payload, MoongladeJsonSerializerOptions.Default)
        }, ct);
    }
}

This keeps the web request path short. The user action only persists a notification message. Actual email delivery happens later.

The Outbox Table

The outbox message entity stores everything needed to process, retry, and diagnose email delivery.

public class EmailOutboxMessageEntity
{
    public Guid Id { get; set; }
    public string MessageType { get; set; }
    public string DistributionList { get; set; }
    public string MessageBody { get; set; }
    public EmailOutboxMessageStatus Status { get; set; }
    public int AttemptCount { get; set; }
    public DateTime CreatedTimeUtc { get; set; }
    public DateTime? LastAttemptTimeUtc { get; set; }
    public DateTime? NotBeforeUtc { get; set; }
    public DateTime? LockedUntilUtc { get; set; }
    public string LockedBy { get; set; }
    public DateTime? SentTimeUtc { get; set; }
    public string LastError { get; set; }
    public Guid ConcurrencyToken { get; set; } = Guid.NewGuid();
}

The dequeue path has an index optimized for pending and retryable messages:

builder.HasIndex(
    nameof(EmailOutboxMessageEntity.Status),
    nameof(EmailOutboxMessageEntity.NotBeforeUtc),
    nameof(EmailOutboxMessageEntity.LockedUntilUtc),
    nameof(EmailOutboxMessageEntity.CreatedTimeUtc))
    .HasDatabaseName("IX_EmailOutboxMessage_Dequeue");

The status model is simple:

Pending -> Processing -> Succeeded
Pending -> Processing -> Failed -> Processing -> Succeeded
Pending -> Processing -> Failed -> DeadLettered

Claiming Messages With A Lease

The outbox store claims one available message at a time. A worker sets LockedBy, LockedUntilUtc, increments AttemptCount, and updates the concurrency token.

entity.Status = EmailOutboxMessageStatus.Processing;
entity.LockedBy = request.WorkerId;
entity.LockedUntilUtc = request.UtcNow.Add(request.LeaseDuration);
entity.LastAttemptTimeUtc = request.UtcNow;
entity.AttemptCount++;
entity.ConcurrencyToken = Guid.NewGuid();

await db.SaveChangesAsync(cancellationToken);

Claimable messages include:

return db.EmailOutboxMessage.Where(m =>
    (m.Status == EmailOutboxMessageStatus.Pending ||
     m.Status == EmailOutboxMessageStatus.Failed ||
     (m.Status == EmailOutboxMessageStatus.Processing && m.LockedUntilUtc <= utcNow)) &&
    (m.NotBeforeUtc == null || m.NotBeforeUtc <= utcNow) &&
    (m.LockedUntilUtc == null || m.LockedUntilUtc <= utcNow));

This gives the system basic durability:

pending messages survive app restarts
failed messages can be retried later
stale processing locks can be recovered
multiple workers can avoid processing the same message through optimistic concurrency

The Background Worker

Email delivery runs in an ASP.NET Core hosted service.

public class EmailOutboxWorker(
    IServiceScopeFactory scopeFactory,
    IOptions<EmailOutboxWorkerOptions> options,
    ILogger<EmailOutboxWorker> logger) : BackgroundService
{
    private readonly string _workerId = $"{Environment.MachineName}-{Guid.NewGuid():N}";

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        var workerOptions = options.Value;
        if (!workerOptions.Enabled)
        {
            logger.LogInformation("EmailOutboxWorker is disabled.");
            return;
        }

        logger.LogInformation("EmailOutboxWorker started with worker ID {WorkerId}.", _workerId);

        while (!stoppingToken.IsCancellationRequested)
        {
            try
            {
                await ProcessBatchAsync(workerOptions, stoppingToken);
                await Task.Delay(workerOptions.PollInterval, stoppingToken);
            }
            catch (OperationCanceledException)
            {
                break;
            }
            catch (Exception ex)
            {
                logger.LogError(ex, "Error in EmailOutboxWorker.");
                await Task.Delay(workerOptions.PollInterval, stoppingToken);
            }
        }
    }
}

The worker is intentionally boring. It polls, processes a small batch, waits, and repeats.

That is enough for Moonglade’s workload.

Processing, Retry, And Dead Letter

The processor validates the message, builds the email, sends it to each recipient, and then updates the outbox state.

var failures = await SendToRecipientsAsync(message, recipients, cancellationToken);

if (failures.Count == 0)
{
    await outboxStore.CompleteAsync(message.Id, utcNow, cancellationToken);
    return;
}

if (failures.Count != recipients.Length)
{
    await outboxStore.CompleteAsync(message.Id, utcNow, cancellationToken);
    return;
}

var transientFailures = failures.Count(f => f.Kind == EmailDeliveryFailureKind.Transient);

if (transientFailures == 0)
{
    await outboxStore.DeadLetterAsync(
        new EmailOutboxFailure(message.Id, errorMessage, utcNow),
        cancellationToken);
    return;
}

await outboxStore.MarkFailedAsync(
    new EmailOutboxFailure(message.Id, errorMessage, utcNow),
    utcNow.Add(GetRetryDelay(message.AttemptCount)),
    cancellationToken);

The retry delay grows until it reaches the configured maximum.

private TimeSpan GetRetryDelay(int attemptCount)
{
    var delay = options.Value.InitialRetryDelay;

    for (var i = 1; i < attemptCount; i++)
    {
        delay = delay + delay;
        if (delay >= options.Value.MaxRetryDelay)
        {
            return options.Value.MaxRetryDelay;
        }
    }

    return delay;
}

This is not a full message broker. It is a small durable outbox designed for a personal blog.

Dependency Injection

The email system is registered as part of the main application.

services.AddScoped<DbEmailNotificationQueue>();
services.AddScoped<IEmailOutboxStore>(sp => sp.GetRequiredService<DbEmailNotificationQueue>());
services.AddScoped<IEmailNotificationQueue>(sp => sp.GetRequiredService<DbEmailNotificationQueue>());
services.AddScoped<IEmailOutboxMessageProcessor, EmailOutboxMessageProcessor>();
services.AddHostedService<EmailOutboxWorker>();

The same database-backed implementation serves both roles:

IEmailNotificationQueue for enqueueing
IEmailOutboxStore for worker-side claiming and state transitions

Configuration

The outbox worker is configured under the main Email section.

{
  "Email": {
    "Provider": "AzureCommunication",
    "OutboxWorker": {
      "Enabled": true,
      "BatchSize": 5,
      "PollIntervalSeconds": 30,
      "LeaseDurationSeconds": 300,
      "MaxAttempts": 3,
      "InitialRetryDelaySeconds": 60,
      "MaxRetryDelaySeconds": 3600
    }
  }
}

Email:OutboxWorker:Enabled=false stops in-process delivery but does not prevent enqueueing. That can be useful if only one app instance should process the outbox.

A Linux Docker Issue

After deploying to Azure App Service on Linux with Docker, I saw this error repeatedly:

System.UnauthorizedAccessException:
Access to the path '/app/mailConfiguration.xml' is denied.

The container runs as the non-root app user. The published files in /app are owned by root and should be treated as read-only.

The issue was caused by initializing the email template helper with a file path. Internally, that path-based constructor required write access.

The fix was to load the XML file explicitly with read-only access, deserialize it, and pass the configuration object to the email helper.

private static MailConfiguration LoadMailConfiguration()
{
    var configSource = Path.Join(AppContext.BaseDirectory, "mailConfiguration.xml");
    if (!File.Exists(configSource))
    {
        configSource = Path.Join(AppContext.BaseDirectory, "Moonglade.Email", "mailConfiguration.xml");
    }

    var serializer = new XmlSerializer(typeof(MailConfiguration));
    using var stream = File.Open(configSource, FileMode.Open, FileAccess.Read, FileShare.Read);

    return serializer.Deserialize(stream) as MailConfiguration
        ?? throw new InvalidOperationException("Configuration file for EmailHelper is invalid.");
}

Then DI can create the helper without requiring write access to /app.

services.AddSingleton<IEmailHelper>(_ => new EmailHelper(LoadMailConfiguration()));

I verified the fix locally with Docker Desktop:

the container ran as user app
/app/mailConfiguration.xml was owned by root
writing to the file failed with Permission denied
EmailOutboxWorker started successfully
no UnauthorizedAccessException appeared in the logs

Why This Design Fits Moonglade

This migration is not about building a better queue than Azure Storage Queue.

It is about choosing the right amount of infrastructure.

For Moonglade, the email workload is small. The important requirements are:

do not block user requests while sending email
persist notifications across app restarts
retry transient failures
avoid losing messages
reduce Azure dependencies
keep deployment simple

A database outbox meets those requirements with fewer moving parts.

Deprecating The Standalone Repository

With email processing integrated into the main application, the standalone Moonglade.Email Azure Functions repository is deprecated and will be archived.

Future email notification development will happen in the main Moonglade repository.

That leaves Moonglade with a simpler model:

one application
one deployment
one database
one configuration surface
one place to maintain email behavior

Final Thoughts

Azure Functions and Azure Storage Queue are good services. They were not the problem.

The problem was that the architecture had become larger than the product requirement.

For a personal blog, reliability matters, but simplicity matters too. Moving email notifications from Azure Functions into Moonglade’s own database outbox keeps delivery asynchronous and durable while removing an entire external service from the deployment.

That is the kind of tradeoff I want Moonglade to make more often.

Azure App Service Pitfalls: Why a PostgreSQL Migration Fails in Production

2026-07-21T09:22:24Z

I recently migrated a small production database from SQL Server to PostgreSQL for my URL forwarder project, Elf. The application already supported PostgreSQL, so the migration sounded straightforward:

Export data from SQL Server.
Import it into PostgreSQL.
Update the Azure App Service connection string.
Restart the app.

In reality, the database migration itself was the easy part. The painful part was Azure App Service configuration and networking.

This post documents what happened, what failed, and what I learned.

The Setup

The source database was a small SQL Server database running on LocalDB:

(localdb)\MSSQLLocalDB
Database: elf

The target production PostgreSQL instance was running in Docker on an Azure VM:

PostgreSQL 18
Database: elfprod
Port: 5432

The application was hosted on Azure App Service for Linux containers:

App Service: elf-forwarder
Region: West US 2
Plan: PremiumV4 P0v4
Container: ediwang/elf:latest

The PostgreSQL VM lived in another Azure subscription and region.

Step 1: Test the Migration Locally First

Before touching production, I created a local PostgreSQL container and migrated the SQL Server data into it.

The source database had only a few tables:

Link:             20 rows
Tag:               3 rows
LinkTag:          26 rows
LinkTracking:  4,319 rows
ElfConfiguration: 0 rows

I wrote a small .NET migration utility using:

Microsoft.Data.SqlClient for SQL Server
Npgsql for PostgreSQL

The tool copied explicit IDs, preserved timestamps as UTC, reset PostgreSQL sequences, and then performed row-by-row, column-by-column verification.

The first local test passed. I also ran the app locally against PostgreSQL and verified the basic read/write flows.

Step 2: Production Migration

For production, I created a new PostgreSQL database:

elfprod

Then I applied the PostgreSQL schema already included in the project.

The first production migration attempt used normal parameterized INSERT statements. It worked locally, but over the public network it was too slow, especially for the LinkTracking table. Since the migration was inside a transaction, I stopped it safely and confirmed the target tables were still empty.

I then changed the migration utility to use PostgreSQL binary COPY.

That made the production migration complete in seconds.

Final migration result:

Link:             20 rows
Tag:               3 rows
LinkTag:          26 rows
LinkTracking:  4,319 rows
ElfConfiguration: 0 rows

The migration tool verified every row and every column against SQL Server successfully.

Step 3: Only Read-Only Validation in Production

After migration, I avoided any production write tests.

I only performed read-only validation:

table counts
foreign key consistency
sequence values
application health endpoint
read-only list endpoints

The application read tests confirmed:

Health endpoint: 200
Link total rows: 20
Tag rows: 3
Tracking rows: 4,319

So far, everything looked good.

Then I updated Azure App Service.

That is where things got ugly.

Problem 1: Azure App Service "Connection Strings" Did Not Behave as Expected

The application reads the database connection string like this:

configuration.GetConnectionString("ElfDatabase")

For SQL Server, using the App Service "Connection strings" blade had worked before.

After switching to PostgreSQL, the app failed during startup:

Failed to connect to database
System.InvalidOperationException: Database connection failed

The confusing part was that Database__Provider=PostgreSql was correctly set. The app was selecting the PostgreSQL provider. But the actual ElfDatabase connection string was empty from the application's point of view.

The fix was to stop relying on the App Service "Connection strings" mapping and explicitly set the ASP.NET Core configuration key as an App Setting:

Database__Provider=PostgreSql
ConnectionStrings__ElfDatabase=Host=<host>;Port=5432;Database=elfprod;Username=<user>;Password=<password>;SSL Mode=Disable;

For ASP.NET Core, this maps directly to:

ConnectionStrings:ElfDatabase

After that, the application received the correct connection string.

But it still could not connect.

Problem 2: The App Service Service Tag Was Not Enough

The PostgreSQL VM had an NSG rule allowing this source:

AppService.WestUS2

The App Service was in West US 2, so this looked correct.

The VM was healthy:

PostgreSQL listening on 0.0.0.0:5432
Docker port mapping: 0.0.0.0:5432->5432

The NSG had a rule allowing TCP 5432 from AppService.WestUS2.

Still, the app timed out when connecting to PostgreSQL.

The key detail was the App Service Plan:

PremiumV4 P0v4

For this plan, Azure reported empty values for:

outboundIpAddresses
possibleOutboundIpAddresses

That means I could not simply whitelist a stable outbound IP list. More importantly, the actual outbound traffic did not appear to be covered by the AppService.WestUS2 service tag in this case.

The fix was to allow a broader regional Azure service tag:

AzureCloud.westus2

The final NSG rule was:

Name:        PGSQL_AZURECLOUD_WUS2
Priority:    199
Source:      AzureCloud.westus2
Protocol:    TCP
Port:        5432
Access:      Allow

After adding that rule and restarting the App Service, the application started successfully.

The logs finally showed:

Database connection successful
Database is already initialized with all required tables
Application initialization completed successfully
Now listening on: http://[::]:8080

Lessons Learned

1. Test the database migration separately from the app migration

The actual SQL Server to PostgreSQL migration was not the hard part. It was controllable, verifiable, and repeatable.

Having a standalone migration tool was useful because it allowed me to verify the data independently from Azure App Service.

2. Use PostgreSQL `COPY` for real migrations

Parameterized inserts are fine for tiny local tests, but they are not ideal over a network.

PostgreSQL binary COPY made the production migration much faster while keeping the same verification logic.

3. Do not assume App Service "Connection strings" map cleanly for Linux containers

For ASP.NET Core apps, especially Linux container apps, I now prefer setting the exact configuration key explicitly:

ConnectionStrings__ElfDatabase

This avoids ambiguity around App Service connection string prefixes such as SQL Server, PostgreSQL, MySQL, or custom connection string mappings.

4. Service tags can be misleading

AppService.WestUS2 sounds like it should cover App Service outbound traffic from West US 2.

In this case, it did not solve the problem.

For PremiumV4, where App Service outbound IPs were not listed, AzureCloud.westus2 was the practical fix.

This is broader than I would like, but it was still limited to:

Region: West US 2
Protocol: TCP
Port: 5432

5. Health checks are not enough if startup can fail softly

The application could still listen on HTTP even after database initialization failed. That made a simple HTTP check insufficient.

The reliable signal was the application log:

Database connection successful
Database is already initialized with all required tables

For future improvements, the app should probably expose a stricter database-aware health check.

Final Result

After fixing both App Service configuration and NSG rules, the application successfully connected to the new PostgreSQL database.

The migration completed with verified data integrity:

Link:             20 rows
Tag:               3 rows
LinkTag:          26 rows
LinkTracking:  4,319 rows
ElfConfiguration: 0 rows

The final issue was not PostgreSQL, EF Core, or the migration logic.

It was Azure platform behavior that looked correct from the portal but did not match the runtime reality.

The main takeaway: when migrating database backends on Azure App Service, validate three things separately:

The app's actual runtime configuration.
The database server's listener and firewall.
The real network path between App Service and the database.

Do not trust the names of Azure UI fields or service tags too much. Verify what the app actually sees and what the network actually allows.

PowerShell to Enable SQL Server Express Remote Access

2024-01-18T05:52:41Z

Problem

SQL Server Express is a free edition of Microsoft's SQL Server, which is a relational database management system (RDBMS). It is designed for small-scale applications and for users who are getting started with learning about databases and SQL Server. However, remote access is not enabled by default on this SKU. To access a SQL Server Express instance from the network, we have to do a few steps. There's plenty of guide on the internet telling you how to enable remote access via GUI, but I would like to do it from PowerShell, so that I can integrate those steps in automated environment.

Solution

Steps

To enable remote access for SQL Server Express, the general steps are

Check "Allow remote connections to this server" checkbox (Already checked in a new SQL Server 2022 Express installation)

Enable TCP/IP Protocol
Set TCP port to 1433
Set Windows Firewall to allow TCP 1433
Restart related services

You can refer to a GUI guide here: https://www.apesoftware.com/calibration-control/help/sql-remote-connections

PowerShell

Thanks to Azure Open AI, I don't have to work 996 this time to make the PowerShell script. The script generally is automating the steps above.

# Run this script as an Administrator

# Step 1: Set SQL Server Browser service to start automatically and start the service
Write-Host "Setting SQL Server Browser service to Automatic and starting the service..."
Set-Service 'SQLBrowser' -StartupType Automatic
Start-Service 'SQLBrowser'

# Step 2: Enable TCP/IP protocol for SQL Server
# Note: This step requires SQL Server Configuration Manager to be installed.
Write-Host "Enabling TCP/IP protocol for SQL Server..."
$sqlServerConfigManager = Get-WmiObject -Namespace "root\Microsoft\SqlServer\ComputerManagement16" -Class "ServerNetworkProtocol"
$tcpIp = $sqlServerConfigManager | Where-Object { $_.InstanceName -eq 'SQLEXPRESS' -and $_.ProtocolName -eq 'Tcp' }
$tcpIp.SetEnable()

# Step 3: Set the TCP Port for IPAll to 1433
Write-Host "Setting the TCP Port for IPAll to 1433..."
$regPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQLServer\SuperSocketNetLib\Tcp\IPAll"
Set-ItemProperty -Path $regPath -Name TcpPort -Value "1433"
Set-ItemProperty -Path $regPath -Name TcpDynamicPorts -Value ""

# Step 4: Create a firewall rule to allow TCP port 1433
Write-Host "Creating a firewall rule for TCP port 1433..."
New-NetFirewallRule -DisplayName "SQL Server Remote Access" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow

Write-Host "SQL Server Express should now accept remote connections."

Restart-Service 'MSSQL$SQLEXPRESS'
Restart-Service 'SQLBrowser'

Please note, this script is for SQL Server 2022 Express, with default instance name "SQLExpress". If you are targeting other version of SQL Server, you should change these items in the script.

ComputerManagement16
MSSQL16.SQLEXPRESS
SQLEXPRESS

To connect to your SQL Server Express instance via network. There are also a few things to keep in mind.

Add port number in server name, that is "IP\SQLExpress,1433", NOT "IP\SQLExpress".
Trust server certificate if SSMS throw error about certificate.

Import Data to Azure Storage Table from SQL Server

2020-06-11T06:46:24Z

Recently, there was a demand to switch the data storage from SQL Server database to Azure Storage Table. However, neither SSMS nor Azure Portal provide a direct import function. You may now consider writing a tool to import data, but actually it isn't necessary. I still managed to do it within mouse clicks.

But the first thing to warn everyone is that relational databases like SQL Server are not the same as the NoSQL service provided by Azure. Before proceeding, please research and make sure your business is really suitable for the Azure Storage Table (or Cosmos Table).

Preparation

Of course you need an Azure Storage Account, create an empty table in it to import data, for example: LinkTracking

Download and install Microsoft Azure Storage Explorer

Download and install SQL Server Management Studio (Windows only) or Azure Data Studio (cross-platform)

Export Data

We need to export the data in the SQL Server table to CSV format in order to import into Azure Storage Table. But we have to do some processing on the data before it can be used.

Taking the export of single table data as an example, Azure Storage Table requires two necessary fields: PartitionKey, RowKey. The SQL Server tables often use one or more columns as the primary key, and there is no fixed name constraints. Therefore, the first thing we have to deal with is the primary key.

My LinkTracking table uses a GUID-type Id as the primary key that can convert to RowKey. As for PartitionKey, the original table does not have that, we can create a fixed string, such as'LT996'.

You can get PartitionKey and RowKey through a simple SELECT statement:

SELECT
    'LT996' AS PartitionKey,
    lt.Id AS RowKey
    -- other fields...
FROM LinkTracking lt

Then we need to format certain data types like the DateTime, it must be converted to ISO 8601 format. Luckily, in T-SQL we an convert a DateTime to ISO 8601 by:

CONVERT(char(30), <DateTime>,126)

If your table has bit representing bool values, we can use a CASE WHEN statement.

CASE 
    WHEN [IsEnabled] = '0' THEN 'false'
    WHEN [IsEnabled] = '1' THEN 'true'
END AS IsEnabled

For NULL values in string type, we must use an empty string or you will end up with string value "NULL".

CASE 
    WHEN l.AkaName IS NULL THEN ''
    WHEN l.AkaName IS NOT NULL THEN l.AkaName
END AS AkaName

In my example, I don't have these two, so the final SQL for me is:

SELECT
    'LT996' AS PartitionKey,
    lt.Id AS RowKey,
    lt.LinkId, lt.UserAgent, lt.IpAddress,
    CONVERT(char(30), lt.RequestTimeUtc,126) AS RequestTimeUtc
FROM LinkTracking lt

Now, with our SQL in place, we are ready to export our data to CSV, there are three ways to do it.

For a few amount of data

If the amount of data in your table is not very large, you can use Azure Data Studio, a cross-platform tool to complete the export operation.

After executing the SQL statement in Azure Data Studio, click the Export to CSV button in the toolbar to the right of the result grid to save the result as a CSV file with column names.

But this method has disadvantages. The result grid has a limit on the amount of data. It not only limits the number of entries, but also the number of characters displayed in the column. If you encounter a large amount of data in an enterprise scenario, it will definitely blow up. So there are the other two methods for exporting data.

For general amount of data

The classic old but not obsolete SSMS provides a special data export wizard that supports the CSV format. There are also two export methods.

Export using SQL statement

We can still use the SQL we just wrote to export the data, but this time it will no longer be exported from the results grid (although SSMS supports this, but it also faces the data amount limit).

Right-click on the database and select Tasks-Export Data

Select the data source as SQL Server Native Client and connect to your own database.

Then choose Destination as Flat File Destination and specify a CSV path as the destination.

Choose "Write a query to specify the data to transfer"

Enter the SQL we got

Set row delimiter matches Windows style, and column delimiter as comma.

Click Next until export success.

Export from View

Another method is to create a View, still using the same SQL statement.

The data export method for View is almost the same as before. The only difference is to select "Copy data from one or more tables or views" in "Specify Table Copy or Query" step.

Import Data

Start Azure Storage Explorer, open the target table which the data would be imported into, and click Import on the toolbar.

Select the CSV file just exported, check and change the data type if necessary for each field. In my example, I only need to change RequestTimeUtc to DateTime type.

Click Insert, and then your data will be imported to Azure Storage Table.

You can also verify the data on Azure Portal.

SQL Server Export Data Tier Application via Command Line

2019-05-20T02:08:13Z

We can use SSMS to export data-tier application for an SQL Server database. I've been using this method for several years on my local machine.

However, to make things work under automated environments, like CI/CD environments, or some automated scheduled tasks. We usually need to do it under the command line.

Microsoft has provided a cross-platform command-line tool that can import/export DAC: sqlpackage.exe

It's written in .NET and currently in preview for Linux and macOS. For Windows, you can download and install it here: https://go.microsoft.com/fwlink/?linkid=2087429

It installed to C:\Program Files\Microsoft SQL Server\150\DAC\bin, but it doesn't add to the PATH environment variable yet. So, in order to use it, we have to manually switch to the installation directory first. Or perhaps we should configure it to PATH on a server.

To export a data-tier application, specify 3 parameters:

/Action:{Extract|DeployReport|DriftReport|Publish|Script|Export|Import} Specifies the action to be performed. (short form /a), we will only use Export in this example.
/SourceConnectionString:<string>Specifies a valid SQL Server/Azure connection string to the source database. If this parameter is specified it shall be used exclusively of all other source parameters. (short form /scs)
/TargetFile:<string> Specifies a target file (i.e., a .dacpac files) to be used as the target of action instead of a database. If this parameter is used, no other target parameter shall be valid. This parameter
shall be invalid for actions that only support database targets. (short form /tf)

Example:

SqlPackage.exe /a:export /scs:"Server=(local);Database=moonglade-dev;Trusted_Connection=True;" /tf:"D:\moonglade-dev-20190520.bacpac"

And my bacpac file is there:

Merge Data for N-N Relationship Tables in T-SQL

2017-01-29T03:32:12Z

Background

Recently, I have been organizing my blog data and needed to perform an operation to merge article categories. On my blog, articles and categories have a many-to-many relationship. That is, one article can belong to multiple categories, and one category can contain multiple articles. This is a very typical many-to-many relationship, and I use a many-to-many table with a composite primary key to associate this data.

What I need to do is move articles from the "DotNetBeginner" category to the "CSharpAndDotNet" category. However, since some of the articles originally in the "DotNetBeginner" category also belong to "CSharpAndDotNet", directly updating the association table would result in duplicate composite keys, which would cause issues.

To put it more intuitively, write an SQL statement to query the data from the original category (DotNetBeginner) and the target category (CSharpAndDotNet):

DECLARE @SourceCatId     AS UNIQUEIDENTIFIER,
        @TargetCatId     AS UNIQUEIDENTIFIER
 
SET @SourceCatId = '678A4011-40E0-4F75-BC23-1FFC25B72D4A'
SET @TargetCatId = 'D58043FF-B3CB-43DA-9067-522D76D21BE3'

SELECT p.Title,
       c.Name
FROM   PostCategory         AS pc
       INNER JOIN Post      AS p
            ON  p.Id = pc.PostId
       INNER JOIN Category  AS c
            ON  c.Id = pc.CategoryId
WHERE  pc.CategoryId IN (@SourceCatId, @TargetCatId)

The result is shown in the following figure:

The article "C#字符和ASCII码互转" will encounter issues if directly updated to the CSharpAndDotNet category because there is already an existing record in the PostCategory table. However, an article like "上海轨道交通……" can be directly updated without any issues.

Solution

The approach to solve this problem is divided into two steps:

Delete records that will break the composite primary key, such as those in "C#字符和ASCII码互转".
Update the related table, changing the ID of the old category to the new category.

First, we need to identify which records meet the criteria for deletion. Find the IDs of these articles and use a GROUP BY HAVING clause to query them.

SELECT pc.PostId
FROM   PostCategory AS pc
WHERE  pc.CategoryId IN (@SourceCatId, @TargetCatId)
GROUP BY
       pc.PostId
HAVING COUNT(*) >= 2

The results are 10 items:

These 10 articles simultaneously belong to the DotNetBeginner, CSharpAndDotNet, and other categories.

To display intuitively and facilitate subsequent deletion operations, define a table variable called temp, and then verify which articles they are.

DECLARE @Temp TABLE (PostId UNIQUEIDENTIFIER)

INSERT INTO @Temp
  (
    PostId
  )(
       SELECT pc.PostId
       FROM   PostCategory AS pc
       WHERE  pc.CategoryId IN (@SourceCatId, @TargetCatId)
       GROUP BY
              pc.PostId
       HAVING COUNT(*) >= 2
   )
------------------------------------------------------------------------------
SELECT --pc.PostId
       p.Title,
       --pc.CategoryId,
       c.Name
FROM   PostCategory         AS pc
       INNER JOIN Post      AS p
            ON  p.Id = pc.PostId
       INNER JOIN Category  AS c
            ON  c.Id = pc.CategoryId
WHERE  pc.PostId IN (SELECT t.PostId
                     FROM   @Temp t)

The result was found to be correct:

Then, you can delete all records where the PostId in the @Temp table matches from the PostCategory association table, and the CategoryId corresponds to DotNetBeginner. After that, use an update statement to complete the merging of article categories.

-- Step 1. Delete records that will fuck up the primary key
DELETE 
FROM   PostCategory
WHERE  CategoryId = @SourceCatId
       AND PostId IN (SELECT t.PostId
                      FROM   @Temp t)

-- Step 2. Update old key to new key
UPDATE PostCategory
SET    CategoryId     = @TargetCatId
WHERE  CategoryId     = @SourceCatId

Finally, let's verify that the data has been successfully merged.

Azure SQL数据库Web Tier爆了，如何迁移数据库

2015-03-22T04:00:10Z

今天做了次数据库迁移，目的是开个最新版的Azure SQL Database（V12 Update），然后把博客的数据库迁移到新的server上去。按以往的做法（也就是我曾经写过的《图解：如何将SQL Server数据库迁移到SQL Azure 》），把bacpac文件下载下来，然后import到新的数据里，结果爆了：

爆炸是因为Web Tier和Business Tier在最新版的Azure上面已经被微软撸掉了，SSMS 2014却没有升级，不认识这两个Tier，还在用老的Web Tier。

TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:

The service objective 'Web' specified is invalid. (Microsoft SQL Server, Error: 40804)

经过开荒，最简单的办法如下。

前提条件是，只针对已经在Azure上面running的数据库，并且你已经把export到了存储账户里去了，想要迁移到新版Azure SQL Database Server上。

如果你想把本地的bacpac迁移到最新版Azure SQL Database上，我还没研究出来。估计powershell可以。等我有空去看看。

1. 点击工具栏上的New - DATA SERVICES - SQL DATABASE - IMPORT

2. 在弹出的向导中，BACPAC URL选取你存储账户中已经导出的bacpac。NAME里面给新数据库起个名字，更具自己的需要选择SERVICE TIERS，最后选择新开一个DATABASE SERVER。但是注意，最小的默认也会开2GB，这个向导里是不能改的，最后数据库导完以后记得手工去改，不然一觉醒来钱都没了！！

3. 在向导的第二步中，勾选“ENABLE LATEST SQL DATABASE UPDATE (V12)”，其他还是和正常创建数据库服务器一样填写。

4. 点击确定后，稍等片刻，最新版的Azure SQL Database Server就创建成功了，老的数据库也已经被倒入进来了。

5. 在SSMS（我用的2014）中可以看到新老数据库的对比。

老版本的Azure SQL Database是11.0.9230的，本地的SQL Server 2014是12.0.2000的，最新版的Azure SQL Database是13.0.15的。

我猜测前段时间开始一直无法用SSMS导出bacpac的问题，也和Azure对database tier的升级有关。现在Web和Business都已经“退休”了。但是微软没给SSMS升级真的蛮蛋疼的。

EntityFramework 6 SqlQuery Passes Nullable Type Arguments

2014-09-03T01:38:23Z

有时候我们需要在EF里直接执行参数化的SQL语句，如果有返回，就要把结果映射成C#对象集合。貌似是从EF4.2开始（4.0写法不一样）提供了DbContext.Database.SqlQuery<T>()的API可以直接执行SQL。但是如果碰到可控类型的参数，比如Guid?，就会爆。

看一个例子，定义的SQL语句如下：

string sql = @"SELECT p2.Id                      AS PeriodId,
                      p2.Title                   AS PeriodTitle,
                      u.UserId                   AS UserId,
                      u.DisplayName              AS UserDisplayName,
                      p.Id                       AS ProductId,
                      p.Title                    AS ProductTitle,
                      SUM(upa.Amount)            AS TotalCount,
                      SUM(p.Price * upa.Amount)  AS TotalPrice,
                      upa.Note                   AS Note
               FROM   Product                    AS p
                      INNER JOIN UserProductAssociation AS upa
                           ON  upa.ProductId = p.Id
                      INNER JOIN Orders          AS o
                           ON  o.Id = upa.OrderId
                      INNER JOIN Period          AS p2
                           ON  p2.Id = o.PeriodId
                      INNER JOIN Users           AS u
                           ON  u.UserId = o.UserId
               WHERE  1 = 1
                      AND (@userId IS NULL OR u.UserId = @userId)
                      AND (@periodId IS NULL OR p2.Id = @periodId)
               GROUP BY
                      u.UserId,
                      u.DisplayName,
                      p2.Id,
                      p2.Title,
                      p.Id,
                      p.Title,
                      upa.Note";

其中@userId和@periodId均为Guid?类型的参数，比如传进来的时候是这样的：

public ActionResult GetStatGridData(..., Guid? userId, Guid? periodId)
...

如果你这样写：

_db.Database.SqlQuery<GetPersonStats_Result>(sql, new[]
            {
                userId, 
                periodId
            }).ToList();

就会爆成这样：

No mapping exists from object type System.Nullable`1[[System.Guid, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]][] to a known managed provider native type.

查了一些资料，说是要转DBNull进去，于是你这样写：

_db.Database.SqlQuery<GetPersonStats_Result>(sql, new[]
            {
                userId ?? (object)DBNull.Value, 
                periodId ?? (object)DBNull.Value
            }).ToList();

结果还是爆：

Must declare the scalar variable "@userId".

最终发现，你得这样写：

_db.Database.SqlQuery<GetPersonStats_Result>(sql,
            new SqlParameter("@userId", userId ?? (object)DBNull.Value),
            new SqlParameter("@periodId", periodId ?? (object)DBNull.Value)).ToList();

就不会爆。

另外，这种写法不适用于SQL CE （已在EF5 + SQL CE上zuo die过）。不知道这是EF的bug还是我SB。总之写下此文以警后人。

EntityFramework中使用.Include()做饥饿加载可能产生的性能问题

2013-10-04T01:25:44Z

这几天在码新版博客程序，因为文章表字段太多，手贱把几个相关列拆分到了1-1的表中。比如Post 1-1 PostPublish，Post 1-1 PostExtension。但是性能突然比以前差了一点。

和这两张相关表直接交互的是这么一段代码：

var query = Repository.Select<Post>().Include(p => p.PostExtension).Include(p => p.PostPublish)
                      .Where(p => p.PostPublish.IsPublished &&
                                  (authorName == null ||
                                  String.Compare(p.Author, authorName, StringComparison.OrdinalIgnoreCase) == 0) &&
                                  (categoryId == null || p.Categories.Select(c => c.Id).Contains(categoryId.Value)))
                      .OrderByDescending(p => p.PostPublish.PubDate)
                      .Skip(startRow)
                      .Take(pageSize);

在拆表之前，因为所有字段都在Post表本身，所以EF并不需要去Join PostPublish和PostExtension。拆表之后，我想使用饥饿加载，在读取Post表的时候就把PostPublish和PostExtension一起带走，本以为性能会更好，没想到这货生成的SQL有一段是这样的：

...
    INNER JOIN [dbo].[PostPublish] AS [Extent2] ON [Extent1].[Id] = [Extent2].[PostId]
    LEFT OUTER JOIN [dbo].[PostPublish] AS [Extent3] ON [Extent1].[Id] = [Extent3].[PostId]
    LEFT OUTER JOIN [dbo].[PostExtension] AS [Extent4] ON [Extent1].[Id] = [Extent4].[PostId]
    WHERE [Extent2].[IsPublished] = 1 ) AS [Filter1]
LEFT OUTER JOIN [dbo].[PostPublish] AS [Extent5] ON [Filter1].[Id] = [Extent5].[PostId]
...

里面有3个LEFT JOIN，并且PostPublish被JOIN了两次，结果执行计划就是这样的：

而去掉两个Include后，程序当然逻辑还是正确的，因为EF本身就可以延迟加载相关表。于是生成的SQL就是这样的：

...
    INNER JOIN [dbo].[PostPublish] AS [Extent2] ON [Extent1].[Id] = [Extent2].[PostId]
    WHERE [Extent2].[IsPublished] = 1 ) AS [Filter1]
LEFT OUTER JOIN [dbo].[PostPublish] AS [Extent3] ON [Filter1].[Id] = [Extent3].[PostId]
...

这时候因为我在当前query里没有用到PostExtension表，首先就少JOIN了一张表，并且PostPublish也只要JOIN一次就可以了。现在的执行计划是这样的：

居然一个LEFT JOIN都没了，虽然不明白SQL里的LEFT JOIN是怎么在执行计划里被撸掉的，但是觉得很厉害的样子。并且性能也有了提升，非常的碉。所以以后用EF的时候不要自作聪明，EF在一般情况下可能比你更聪明。。。

图解：如何将SQL Azure数据库备份到本地SQL Server

2013-04-29T13:04:07Z

用了两天SQL Azure后，发现SQL Azure数据库没有办法做传统意义上的“bak”备份，但我们确实很需要能够随时将数据库备份到本地，以更灵活的使用。所以今天就开荒了一下操作方法，并分享给大家。这样一来，我们既可以把SQL Server迁移到SQL Azure，又可以反过来把SQL Azure撸回SQL Server，这是一个比较完整的装逼过程，妹子们一定会赞不绝口。

步骤如下：

1. 启动SSMS2012，在目标SQL Azure数据库上点右键，选择“Export Data-tier Application”。

2. 在Export Settings选项卡里制定本地的备份路径，现在已经是“bacpac”拓展名的了。。。

3. 然后点击Next，确认后就向导就开始撸了。

4. 撸完以后会显示Operation Complete的消息，现在本地备份文件已经创建完成了。

5. 在本地SQL Server数据库目录上点击右键，选择"Import Data-tier Application"

6. 在Import Settings选项卡中选择“Import from local disk”，并选择刚才撸出来的文件。

7. 确认新数据库的名字和保存位置，然后点Next

8. 之后向导就开始把备份文件反撸回本地SQL Server了。

9. 撸完以后，同样会显示Operation Complete的消息。

10. 刷新数据库列表，可以看到我们的数据库已经成功从SQL Azure上被撸回来了~

图解：如何将SQL Server数据库迁移到SQL Azure

2013-04-28T12:52:13Z

最近为了进一步装逼，决定将本博客的数据库从SQL Server 2008 R2迁移到Windows Azure上，即SQL Azure，云数据库。今天花了半天时间成功完成了迁移，把经验拿出来给大家分享。

注意：本文的方法只在SQL Server 2008 R2和SQL Server 2012数据库上试过，其他版本生死未卜。

首先，不管你现在的数据库是MSSQL2008R2的还是MSSQL2012的，你都需要安装SQL Server 2012版的SSMS。这是最方便的迁移方法。如果不想用SSMS，可以试试codeplex上的这个工具：http://sqlazuremw.codeplex.com/

1. 启动SSMS后，在要被迁移的本地数据库上点右键，选择Tasks - Deploy Database to SQL Azure...

2. 在弹出的向导中，点击Connect，连接到你在Windows Azure上创建好的SQL Azure服务器。并在“New database name”里输入目标数据库的名字。这个向导将会按照你的配置在SQL Azure上创建一个新数据库。

3. 点击下一步，进入Summary页面，确认配置是否正确。

4. 如果确认无误，点击“Finish”，向导就开始撸你的数据库了。不过要记住，请确保你的数据库里没有不正常的schema和user（比如从其他数据库服务器恢复到本机数据库就容易有这个问题），不然向导会爆掉。

5. 撸完以后，向导会提示你迁移成功，就OK了。

6. 然后，你得更改现有程序里的连接字符串。以我的ASP.NET博客为例，连接字符串如下：

<add name="ApplicationServices"
         connectionString="Server=tcp:服务器.database.windows.net,1433;Database=EdiBlog_75_AzureMigrationTest;User ID=用户名@服务器;Password=密码;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"
         providerName="System.Data.SqlClient" />
<add name="EdiBlogEntities"
     connectionString="metadata=res://*/Entities.EdiBlog.csdl|res://*/Entities.EdiBlog.ssdl|res://*/Entities.EdiBlog.msl;provider=System.Data.SqlClient;provider connection string=&quot;Server=tcp:服务器.database.windows.net,1433;Database=EdiBlog_75_AzureMigrationTest;User ID=用户名@服务器;Password=密码;Trusted_Connection=False;multipleactiveresultsets=True;Encrypt=True;Connection Timeout=30;&quot;"
     providerName="System.Data.EntityClient" />

注意，EntityFramework的连接字符串一定要加上“multipleactiveresultsets=True”，不然运行时会爆掉。

7. 最后，测试你的应用程序，看看是否能正常操作数据库。这时候你应该能在Azure Portal上看到你的SQL Azure数据库了。如果看不到的话等个十几分钟应该就会刷出来。

Performance tips for Entity Framework

2012-12-09T11:10:07Z

自从我用了EF，每次都很关心是否有潜在的性能问题。所以每次我写LINQ查询，都会使用SQL Profiler看一下实际生成的SQL语句，以便发现潜在的性能问题。也强烈建议大家这么去做，以免日后软件大了出了问题很难查。

一、只选择某列或某些列

有些时候，在C#里写LINQ虽然看着舒服，但性能不一定好，所以有必要做一些调整。比如这种情况：

我需要知道一篇文章的点击数，仅此而已，我可能会写：

context.Post.FirstOrDefault(p => p.Id == postId).Hits;

或者：

context.Post.Find(postId).Hits;

我期待着他们只去数据库里筛选Hits这一列的数据，然而，通过SQL Profiler会发现，这两条语句居然把全部列都给select出来了，访问Hits的操作实际是在内存中进行的。

虽然小表看不出性能问题，但万一你的表里有一列是存文件字节流（byte）的，那这样的操作可能会很慢，并且消耗额外的网络传输，所以不能忽视这个问题。

其实，我只要稍作调整，就能避免这个问题，但会LINQ语句难看一点：

context.Post.Where(p => p.Id == postId).Select(p => p.Hits).FirstOrDefault();

LINQ to SQL最终生成的native sql是这样的：

exec sp_executesql N'SELECT TOP (1) 
[Extent1].[Hits] AS [Hits]
FROM [dbo].[Post] AS [Extent1]
WHERE [Extent1].[Id] = @p__linq__0',N'@p__linq__0 uniqueidentifier',@p__linq__0='850C3A86-6C3D-408B-8099-61EDA559F804'

真正的只select了Hits一个字段。

二、ToList()的问题

其实EF很多时候的性能问题都是关系到查询执行时机的。我们通常的意图是，首先建立一个查询表达式，只是build，而不execute。执行的时机是用到这个表达式结果的时候才去执行。

在公司码程序的时候，我看到好多同事用EF，写完查询喜欢直接调用ToList()方法。有时候这会造成很大的性能问题。因为单纯声明一个linq表达式并不会立即执行SQL查询，然而一旦在后面加上ToList()，就会立即去执行。如果你只是想根据条件选择其中一些数据，而非全部的话，那ToList()以后再筛选，就是从内存里执行了，并不是把你的条件转换成sql的where语句去执行。

var query = from ..... // 建立查询，但不执行
... 
var result = query.ToList(); // 立即执行查询

所以，你应当尽量避免从ToList()后的结果中再去查找自己想要的元素。

三、IQueryable, IEnumerable

在这两个接口的选择上，我偏向使用IQueryable。大部分时候这两个接口在使用上的表现都是一致的，但如果你要做的是一个不确定的查询，意思是这个查询表达式不是一次性确定的，对于它的结果可能由别的类来选择到底select哪些东西，这时候就要用IQueryable。

比如我有一个数据层方法：

public IEnumerable<EdiBlog.Core.Entities.Post> GetAllPost()
{
    return context.Post;
}

很显然，它会被系统中的其他方法调用，而这些调用者希望得到的结果都各不相同。通常的操作就是再拼一个where语句上去：

var myResult = postDa.GetAllPost().Where(...)

但这时，很不幸的是，where语句中的条件并不是转换为native sql去执行的，它是在内存中筛选的。这是一个比较阴的性能问题。所以文章一开始我就建议大家多用SQL Profiler看看自己的LINQ是怎么执行的。

如果把返回类型换成IQueryable，那么你的where语句就可以转化为SQL执行。

public IQueryable<EdiBlog.Core.Entities.Post> GetAllPost()
{
    return context.Post;
}

关于这两个接口，在屌丝论坛StackOverflow上有一个比较好的帖子，大家可以自己看一下：

http://stackoverflow.com/questions/252785/what-is-the-difference-between-iqueryablet-and-ienumerablet

“IEnumerable: IEnumerable is best suitable for working with in-memory collection. IEnumerable doesn’t move between items, it is forward only collection.

IQueryable: IQueryable best suits for remote data source, like a database or web service. IQueryable is a very powerful feature that enables a variety of interesting deferred execution scenarios (like paging and composition based queries).”

在MSDN论坛上也有个比较直观的答案：

IQueryable returns a "queryable" that is a query you could still be enriched before really sending it to the server.

IEnumerable returns a list that is the actual querying took place and you get the results. ToList is isued to force running the query and returning these enumerable results...

So in short :
- use IQueryable if you want to return a base query that could be further enhanced before running it server side (by enumerating its items)..
- use IEnumerable/ToList if you want to return a list that has been retrieved from the db

四、计算个数，Count()和Count

这个是最容易被坑，也是非常严重的一个性能问题。当我们需要统计符合某条件的记录的条数时，我们希望SQL语句是SELECT COUNT(*) ... 这种形式的。然而下面这个看似很自然的写法却会导致不希望的结果：

context.Category.FirstOrDefault(p => p.Name == categoryName).Posts.Count;

这是我博客里用来统计某分类下文章数目的语句，当然，因为发现性能问题，现在已经不是这么写了。它产生的SQL并不是SELECT COUNT，而是分成2条。下面是SQL Profiler抓到的：

exec sp_executesql N'SELECT TOP (1) 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Extent1].[DisplayName] AS [DisplayName]
FROM [dbo].[Category] AS [Extent1]
WHERE [Extent1].[Name] = @p__linq__0',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'ASPNET'

exec sp_executesql N'SELECT 
[Extent2].[Id] AS [Id], 
[Extent2].[Title] AS [Title], 
[Extent2].[Slug] AS [Slug], 
[Extent2].[PubDate] AS [PubDate], 
[Extent2].[PostContent] AS [PostContent], 
[Extent2].[Author] AS [Author], 
[Extent2].[CommentEnabled] AS [CommentEnabled], 
[Extent2].[IsPublished] AS [IsPublished], 
[Extent2].[Hits] AS [Hits], 
[Extent2].[Rators] AS [Rators], 
[Extent2].[Rating] AS [Rating], 
[Extent2].[ExposedToSiteMap] AS [ExposedToSiteMap], 
[Extent2].[DisplayFrom] AS [DisplayFrom], 
[Extent2].[DisplayTill] AS [DisplayTill], 
[Extent2].[LastModifyOn] AS [LastModifyOn], 
[Extent2].[PublishToRss] AS [PublishToRss]
FROM  [dbo].[PostCategory] AS [Extent1]
INNER JOIN [dbo].[Post] AS [Extent2] ON [Extent1].[PostId] = [Extent2].[Id]
WHERE [Extent1].[CategoryId] = @EntityKeyValue1',N'@EntityKeyValue1 uniqueidentifier',@EntityKeyValue1='3FEB11A2-6E36-4DCE-8C02-614BEF7ACC62'

可以看到，EF做了两件事，第一件事是查找Name为"ASPNET"的Category，然后用这个Category的Id去找它所有的Post，最后做Count的其实是.NET在内存里进行的。这显然把我们不需要的信息都给SELECT出来了。我们只需要一个Count，为毛会这么复杂呢？

回顾第一条我所讲过的。不难发现。在FirstOrDefault(...)之后访问的属性，都是在内存里进行的。所以，当我们访问Category.FirstOrDefault(p => p.Name == categoryName)的时候，就生成了第一条SQL语句。紧跟其后的“.Posts”是Category对象的导航属性，EF会用lazy load去加载这个category所有的post，所以就生成了第二条SQL语句。再紧接其后的Count就自然而然在内存里进行了。

如果要让代码尽量去生成LINQ to SQL，有个很简单的原则，就是尽量用LINQ、Lambda表达式，这样EF才可能帮我们翻译。C#里的Count有两种。Enumerable.Count()是方法，List.Count是属性。一旦一个东西变成了List，你再去Count，就必定是在内存里进行的了。

所以，在EF中，要进行Count操作，应该这样写：

context.Post.Count(p => p.Categories.Any(q => q.Name == categoryName));

这时，Count()接受了一个lambda表达式，LINQ to SQL就能准确翻译为“SELECT COUNT”了：

SELECT [GroupBy1].[A1]  AS [C1]
FROM   (
           SELECT COUNT(1)      AS [A1]
           FROM   [dbo].[Post]  AS [Extent1]
           WHERE  EXISTS (
                      SELECT 1 AS [C1]
                      FROM   [dbo].[PostCategory] AS [Extent2]
                             INNER JOIN [dbo].[Category] AS [Extent3]
                                  ON  [Extent3].[Id] = [Extent2].[CategoryId]
                      WHERE  ([Extent1].[Id] = [Extent2].[PostId])
                             AND ([Extent3].[Name] = 'ASPNET')
                  )
       )                AS [GroupBy1]

现在性能要明显好很多～

How to Manually Upgrade Entity Framework 4.0 to 5.0

2012-12-01T00:41:59Z

I have been working on the next version of my blog recently, and one of the updates is upgrading Entity Framework to version 5.0. When I initially set up the blog, I used EF4 that came with VS2010. The upgrade process from version 4.0 has to be done manually; using NuGet for the upgrade can only automatically update to versions 4.1 and above.

Yesterday, I worked on it for a long time, and finally got the website up and running. Here's what I learned from the experience.

Step 1: Install Entity Framework 5.0 from NuGet

First, do not make any changes or deletions to the existing EF4.0 project. Directly install EF5 from NuGet. If your project is based on .NET 4, what you're actually installing is the 4.4 version of the DLL, so the enumeration types of EF5 cannot be used. If you have already upgraded to .NET 4.5, then it is fully EF5.

NuGet will automatically add references and configure the relevant config files for you.

Step 2: Update EDMX

Open an existing EDMX file, right-click on the blank space, and select "Add Code Generation Item"

The new version of EF uses the T4 template to generate entity classes, while in 4.0 it is in an edmx, so we have to choose a template, and there is obviously only one option here:

EF 5.x DbContext Generator

VS will then pop up several dialog boxes asking if you dare to run the build script. Obviously we want to confirm the run. After a while, your new version of the EF entity class is generated:

Step 3: Update the CUD method

Now, your program won't compile. Since EF4.0, the APIs that have been added, deleted, and modified have changed. So we're going to make some changes to the old code.

1. Insert: This is now done using context.Type.Add(object).

2. Delete: This is now done using context.Type.Remove(object).

3. Update operation: Attach is not needed now, use context.Entry.State = System.Data.EntityState.Modified

4. If you also need an ObjectSet<T> type, you must use an adapter to force it:

(context as IObjectContextAdapter).ObjectContext.CreateObjectSet<Type>();

Compile the program again, and it should be ready to use~

给ASP.NET Membership增加权限（Rights）功能

2012-11-09T02:26:26Z

首先说明，本文用的是非常屌丝的办法，针对SQL Server Membership的实现的一个很有局限性的权限模块，而不是真正对ASP.NET Membership本身的拓展。不喜慎入。

一、为什么要给Memebership增加权限系统

我们知道，ASP.NET Membership是基于Role的，其实是个RBAC，没有权限（Rights）功能。也就是说，我们只能够指定某个Role可以做什么事，丧失了一定的灵活性。比如，网站后台有个编辑文章的功能。如果基于Role判断，我只能认为：所有Admin、Editor、Teacher可以编辑文章。在MVC3里，可以用Authorize(Role=”Admin, Editor, Teacher”)的属性做到这一点。然而如果想要动态可配，则比较难了。然而，如果是基于Rights的验证，那我就可以认为：所有具有Edit权限的Role可以编辑文章。这样的话，如果那天我们不希望Teacher编辑文章，就可以把Teacher这个Role的Edit权限去掉，而不用改程序。下面我们看看如何在SQL Server上增加一个Rights功能。

二、数据库更改

假设你已经在SQL Server上配好了ASP.NET Memebership服务（可以使用aspnet_regsql工具），你就会看到一些列的表，其中有个叫“Roles”（也可能叫aspnet_Roles），我们需要做的是建另外两张表。

“Right”表包括了你应用程序中所有的权限枚举，这里我仅仅需要“RightName”字段来表示，你可以可以按需要增加其他字段。“RoleRight”是一张多对多的表，描述了“Roles have rights”的关系。

当然，你也可能需要在这几张表之间增加关联，并且把DELETE RULE设为CASCADE。不过，如果你实在不喜欢做外键关联也没关系，但最终码程序的时候你需要一个更复杂的查询来完成，文章后面会一起介绍。

创建表的SQL脚本如下：

CREATE TABLE [dbo].[Rights](
	[Id] [uniqueidentifier] NOT NULL,
	[RightName] [nvarchar](150) NOT NULL,
 CONSTRAINT [PK_Rights] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[RoleRight](
	[RoleId] [uniqueidentifier] NOT NULL,
	[RightId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_RoleRight] PRIMARY KEY CLUSTERED 
(
	[RoleId] ASC,
	[RightId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[RoleRight]  WITH CHECK ADD  CONSTRAINT [FK_RoleRight_Rights] FOREIGN KEY([RightId])
REFERENCES [dbo].[Rights] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[RoleRight] CHECK CONSTRAINT [FK_RoleRight_Rights]
GO

ALTER TABLE [dbo].[RoleRight]  WITH CHECK ADD  CONSTRAINT [FK_RoleRight_Roles] FOREIGN KEY([RoleId])
REFERENCES [dbo].[Roles] ([RoleId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[RoleRight] CHECK CONSTRAINT [FK_RoleRight_Roles]
GO

接下来，我们插一些测试数据：

如图，这些记录的意思是所有在Administrators这个Role下的User都具有Edit和Create权限。

三、示例代码：ASP.NET MVC3应用程序的代码更改

首先，我们要对MembershipUser类进行拓展，让它支持我们的权限表。假设你用的是Entity Framework，你可以像这样进行拓展：

public static class MembershipExtensions
{
    public static bool HasRight(this MembershipUser user, string[] rights)
    {
        var userRole = user.GetRoles();

        using (var db = new HammerEntities())
        {
            var query = from role in db.Roles
                      where userRole.Contains(role.RoleName)
                      select role.Rights into temp
                      from right in temp
                      where rights.Contains(right.RightName)
                      select right;

            return query.Count() == rights.Length;
        }
    }

    public static string[] GetRoles(this MembershipUser user)
    {
        return System.Web.Security.Roles.GetRolesForUser(user.UserName);
    }
}

测试一下这个方法，EF会在运行时产生以下SQL：

SELECT [Join1].[Id]         AS [Id],
       [Join1].[RightName]  AS [RightName]
FROM   [dbo].[Roles]        AS [Extent1]
       INNER JOIN (
                SELECT [Extent2].[RoleId] AS [RoleId],
                       [Extent3].[Id]     AS [Id],
                       [Extent3].[RightName] AS [RightName]
                FROM   [dbo].[RoleRight]  AS [Extent2]
                       INNER JOIN [dbo].[Rights] AS [Extent3]
                            ON  [Extent3].[Id] = [Extent2].[RightId]
            )               AS [Join1]
            ON  [Extent1].[RoleId] = [Join1].[RoleId]
WHERE  ([Join1].[RightName] IN (N'Edit', N'Create'))
       AND (N'Administrators' = [Extent1].[RoleName])

结果正如我们希望的一样，Administrators的所有权限被列出来了：

刚才在第二节最后提到，如果你不希望给表之间建立关联，必须使用一个复杂查询来完成同样的操作。这个查询如下，运行结果是一样的：

var query = from role in db.Roles
          join roleRight in db.RoleRight on role.RoleId equals roleRight.RoleId
          join right in db.Rights on roleRight.RightId equals right.Id
          where userRole.Contains(role.RoleName) && rights.Contains(right.RightName)
          select right;

现在，你的ASP.NET网站已经具有检查权限的功能了：

[Authorize]
public ActionResult About()
{
    var rightsToCheck = new string[] { "Edit", "Create" };
    if (CheckRight(rightsToCheck))
    {
        return View();
    }
    return new HttpUnauthorizedResult();
}

private bool CheckRight(string[] rights)
{
    var user = Membership.GetUser(User.Identity.Name);
    if (null != user)
    {
        return user.HasRight(rights);
    }
    return false;
}

并且，在ASP.NET MVC3的应用程序中，我们可以用一种更专业的手段去完成验证——作为Attribute。

只要重写AuthorizeAttribute类中的AuthorizeCore()方法，你就可以在MVC3中仅仅用一行代码完成权限验证。RightAuthorizeAttribute代码如下：

public class RightAuthorizeAttribute : AuthorizeAttribute
{
    public string Right { get; set; }

    protected override bool AuthorizeCore(HttpContextBase httpContext)
    {
        return CheckRight(Right.Split(','), httpContext);
    }

    private bool CheckRight(string[] rights, HttpContextBase httpContext)
    {
        var user = Membership.GetUser(httpContext.User.Identity.Name);
        if (null != user)
        {
            return user.HasRight(rights);
        }
        return false;
    }
}

现在，你可以用非常干净的代码来验证权限了：

[Authorize]
[RightAuthorize(Right = "Edit,Create")]
public ActionResult About()
{
    return View();
}

试试访问“About”Action，因为要求登录，所以系统会弹回一个登录页面：

我是以管理员组的用户登录的，所以可以成功看到About页面的内容：

如果我把权限特征改成[RightAuthorize(Right = "Edit,View")]，其中View并不是授权给Administrators的，那么这个Action会再次将我弹回登录页面，因为你没有View的权限了。可以从Debug中看到这一切：

OK，至此我们已经码完了最基本的ASP.NET Memebership权限验证模块～

在Entity Framework中使用LINQ语句分页

2012-09-21T07:31:13Z

我们知道，内存分页效率很低。并且，如果是WebForm的项目，页面上会有巨型ViewState，这必然不好。我自己博客用的是一个存储过程做的分页，用到现在都挺好，没有任何效率问题。后来想到，既然项目里有Entity Framework，那为什么不利用EF完成分页呢～

稍做研究之后发现，EF分页其实很简单。不过一样写文章了，光贴代码是不负责的，还是得稍微介绍一下相关的知识。

一、页数计算

关于分页的基本原理，网上有很多文章，我就不多叙述了。但我发现很多介绍分页的文章里，计算页码公式都掐掉了，广为流传的版本是：

totalPage = totalRecord / pageSize + 1

稍微推敲一下就会发现，这个公式在totalRecord和pageSize正好整除时，会多一页。比如10条记录，每页5条，应该是2页的，但结果是3。然而，不写+1又不行，因为要考虑到记录总数小于页尺寸的情况。现在，许多网站的分页都有这个问题。而我自己用的是下面这个公式，无论如何都不会掐掉的：

totalPage = (totalRecord + pageSize - 1) / pageSize

注意，C#里的“/”是整除。所以这两个公式不是数学意义上的计算公式，呵呵。

二、分页存储过程的原理

（题外话：SQL Server 2012有了新的分页语法，本文只针对SQL Server 2008 R2及以往的版本）

任何分页存储过程的核心语句最后都会拼接为类似下面的这种SQL语句：

SELECT TOP(10) -- pageSize
       *
FROM   (
           SELECT *,
                  ROW_NUMBER() OVER(ORDER BY [Temp].[PubDate] DESC) AS
                  [row_number]
           FROM   [dbo].[Post] AS [Temp]
       ) AS [Temp]
WHERE  [Temp].[row_number] > 10 -- (pageIndex - 1) * pageSize
ORDER BY
       [Temp].[PubDate] DESC

这段SQL获取的是Post表，按PubDate逆序排列后，第二页的数据。

ROW_NUMBER()函数的作用是给查询结果生成行号，这是为了给后面的where语句用的。这个函数的具体介绍请看MSDN：http://msdn.microsoft.com/en-us/library/ms186734.aspx

三、LINQ to SQL分页

我们的目标很明确，就是最终要将LINQ语句翻译到与上面相符的SQL语句。在LINQ to SQL中，Enumerable.Take<TSource>最终会翻译为TOP(n)，而Enumerable.Skip<TSource>和Take结合起来使用，就会翻译成ROW_NUMBER()…OVER…的语句。

所以，上面贴出的SQL代码的等价LINQ是：

Enumerable.OrderByDescending(p => p.PubDate).Skip(10).Take(10);

四、在Entity Framework中对某表分页

用EF对某表做完映射后，我们可以封装一个分页方法，比如：

private static List<Post> GetPostList(int pageIndex, int pageSize)
{
    int startRow = (pageIndex - 1) * pageSize;
    using (var db = new EdiBlogEntities())
    {
        var query = db.Post.OrderByDescending(p => p.PubDate).Skip(startRow).Take(pageSize);
        return query.ToList();
    }
}

对于方法的返回类型，我选择List是因为调用它的程序不需要访问导航属性。如果程序中有访问导航属性的需要，就不能关闭当前的database context，应该要返回IEnumerable<T>类型。

测试代码：

static void Main(string[] args)
{
    int pageIndex = 2;
    int pageSize = 10;
    int totalPage = (GetPostCount() + pageSize - 1) / pageSize;

    Console.WriteLine("List of Posts. Page {0} / {1}, showing {2} rows per page.", pageIndex, totalPage, pageSize);
    Console.WriteLine("---------------------------------------------------------");
    foreach (var item in GetPostList(pageIndex, pageSize))
    {
        Console.WriteLine("{0}, {1}", item.Title, item.PubDate);
    }
}

private static int GetPostCount()
{
    using (var db = new EdiBlogEntities())
    {
        return db.Post.Count();
    }
}

运行结果：

和之前的存储过程分页是一致的：

Store and Read Images via SQL Server in ASP.NET MVC 3

2012-09-20T12:16:27Z

First of all, I don't recommend storing images in the database, especially in a web project. Because file systems can provide static file access and get many benefits such as performance. Storing images in the database has certain limits.

But if you must store images into SQL Server, here's how to do it.

SQL

Image files are in fact, binary data. SQL Server provides us the image type for storing images. So we can create a table like this, with a column named Pic as image type.

CREATE TABLE [dbo].[ImgTest]
(
    [Id]   [uniqueidentifier] NOT NULL,
    [Pic]  [image] NOT NULL,
    CONSTRAINT [PK_ImgTest] PRIMARY KEY CLUSTERED([Id] ASC)WITH (
        PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Then, map this table with Entity Framework. Finally, you will get an entity type like this:

namespace EFImgTest.Models
{
    using System;
    using System.Collections.Generic;

    public partial class ImgTest
    {
        public System.Guid Id { get; set; }
        public byte[] Pic { get; set; }
    }
}

As we can see, Pic column is mapped to byte[] array. Now we need to deal with converting image data to byte[] and backward.

Upload Image to Database

When you submit an image file in MVC3, you need to set Form's enctype to multipart/form-data:

@using (Html.BeginForm("Create", "ImgTest", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    @Html.ValidationSummary(true)
    <fieldset>
        <legend>ImgTest</legend>
        <input type="file" name="imgFile" id="imgFile" />
        <p>
            <input type="submit" value="Create" />
        </p>
    </fieldset>
}

Add an extra parameter HttpPostedFileBase imgFile in the Action to receive the uploaded image. Then, read it into binary data using HttpPostedFile.InputStream.Read().

[HttpPost]
public ActionResult Create(ImgTest imgtest, HttpPostedFileBase imgFile)
{
    if (ModelState.IsValid)
    {
        if (imgFile.ContentLength > 0)
        {
            var imgByte = new byte[imgFile.ContentLength];
            imgFile.InputStream.Read(imgByte, 0, imgFile.ContentLength);
            imgtest.Id = Guid.NewGuid();
            imgtest.Pic = imgByte;
            db.ImgTest.Add(imgtest);
            db.SaveChanges();
        }
        return RedirectToAction("Index"); 
    }
    return View(imgtest);
}

After you uploaded the image, it's data in the database looks like this:

If you would like to insert image data into SQL Server by T-SQL, here's how to do it:

INSERT INTO ImgTest
  (
    Id,
    Pic
  )
SELECT NEWID(),
       BulkColumn
FROM   OPENROWSET(BULK 'D:\Edi\tempfolder\img-5b394358-da2e-4a0a-ab32-a740123a44img-1b702a0a-e147-4e42-b579-7a19bf68b5cd.png', SINGLE_BLOB) AS Pic

Read Image from byte[]

Razor engine doesn't provide built-in methods for displaying images via byte[]. So we need to write a custom Action for converting byte[] into FileContentResult:

public ActionResult GetImage(Guid id)
{
    var imgByte = db.ImgTest.Find(id).Pic;
    return new FileContentResult(imgByte, "image/jpeg");
}

Use it in a view:

<img src="@Url.Content("~/ImgTest/GetImage/" + item.Id)" />

博客数据库升级手记（四）：XML数据的导入

2012-08-27T23:28:02Z

在上一篇文章中，我已经完成了旧数据库的导入。然而，我的博客原先除了用Access数据库保存数据，也大量使用了XML存储，这借鉴自BlogEngine.NET，但后来发现XML存储弊大于利，最适合我的还是传统关系型数据库。因此，我有必要把所有非系统数据迁移到SQL Server上。这里要提一句，系统数据和用户数据是不一样的。系统数据是维持系统程序的运行所需要的数据，用户数据是用户使用系统产生的数据。系统数据包括系统设置（XML保存）、系统菜单等等，而用户数据除了博客文章，还有其创建的页面、友情链接，这些东西原先都是XML存储的，这次要统一迁移到SQL Server上。

首先，要对应XML的数据结构，在SQL Server中建立对应的表，列名和数据类型可以不一样，但要记得方便导入，不然就得用前几篇文章的技巧处理了。本文以友情链接表为例，建立的表如下：

CREATE TABLE [dbo].[FriendLink](
    [Id] [uniqueidentifier] NOT NULL,
    [Title] [nvarchar](100) NOT NULL,
    [Link] [nvarchar](300) NOT NULL,
    [OrderId] [int] NOT NULL,
 CONSTRAINT [PK_FriendLink] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

除了Link字段是重命名自XML中的Url字段，其他的字段数据类型和名称都没有变化。

SQL Server 天生支持XML类型的数据，可以从一坨XML字符串中解析数据到一张表。系统内置了一个叫做sp_xml_preparedocument的存储过程，它在MSDN上的描述是：

读取作为输入提供的 XML 文本，然后使用 MSXML 分析器 (Msxmlsql.dll) 对其进行分析，并提供分析后的文档供使用。分析后的文档对 XML 文档中的各节点（元素、属性、文本和注释等）的树状表示形式。

sp_xml_preparedocument 返回一个句柄，可用于访问 XML 文档的新创建的内部表示形式。该句柄在会话的持续时间内有效，或者通过执行 sp_xml_removedocument 使其在句柄失效前一直有效。

用法是：

DECLARE @docHandle INT –返回的句柄
EXEC sp_xml_preparedocument @docHandle OUTPUT,
     @doc –输入的XML字符串
EXEC sp_xml_removedocument @docHandle –用完以后清理这个对象

另一个需要用到的系统函数是OPENXML。MSDN参阅：http://msdn.microsoft.com/zh-cn/library/ms186918%28v=sql.105%29.aspx

直接介绍语法和概念太抽象，所以我先贴出导入友情链接数据的脚本，然后再讲解：

（为了节省篇幅，我缩减了友情链接的数量）

DECLARE @doc XML

SET @doc = 
    '<?xml version="1.0"?>
	<FriendLinkData>
	  <FriendLink>
		<Id>bf6272bc-7b22-4002-bb43-a84b5997afd5</Id>
		<Title>XNA Develop</Title>
		<Url>http://www.xnadevelop.com/</Url>
		<OrderId>0</OrderId>
	  </FriendLink>
	  <FriendLink>
		<Id>7579ed1e-2a63-410c-9f5b-631c598181e9</Id>
		<Title>蜈蚣s Blog</Title>
		<Url>http://www.wgcss.com</Url>
		<OrderId>1</OrderId>
	  </FriendLink>
	  <FriendLink>
		<Id>74ebcaa9-5aff-474b-ac36-647586a8c0ee</Id>
		<Title>老徐的博客</Title>
		<Url>http://www.54peixun.com/Author/frankxulei</Url>
		<OrderId>2</OrderId>
	  </FriendLink>
	</FriendLinkData>'

DECLARE @docHandle INT

EXEC sp_xml_preparedocument @docHandle OUTPUT,
     @doc
INSERT INTO FriendLink
(
	Id,
	Title,
	Link,
	OrderId
)
SELECT Id,
       Title,
       [Url],
       OrderId
       
FROM   OPENXML(@docHandle, '/FriendLinkData/FriendLink', 2)
       WITH
       (Id UNIQUEIDENTIFIER, Title NVARCHAR(100), [Url] NVARCHAR(300), OrderId INT)

EXEC sp_xml_removedocument @docHandle

这个脚本是很容易理解的，只是有几个地方要注意。首先，OPENXML函数的最后一个参数，是有含义的。它可以是下列值之一：

字节值		说明
0	默认为“以属性为中心”的映射。
1	使用“以属性为中心”的映射。可以与 XML_ELEMENTS 一起使用。这种情况下，首先应用“以属性为中心”的映射，然后对所有未处理的列应用“以元素为中心”的映射。
2	使用“以元素为中心”的映射。可以与 XML_ATTRIBUTES 一起使用。这种情况下，首先应用“以属性为中心”的映射，然后对所有未处理的列应用“以元素为中心”的映射。
8	可与 XML_ATTRIBUTES 或 XML_ELEMENTS 组合使用（逻辑或）。在检索的上下文中，该标志指示不应将已使用的数据复制到溢出属性 @mp:xmltext。

在我的友情链接XML中，使用的是元素保存值，而不是保存在标签的属性中。所以选择的是2。

另外一个小细节就是XML的头部，一定要写成“<?xml version="1.0"?>”不能再多写其他属性了，不然SQL是会爆的。

其他XML数据的导入方式和这个类似，不再重复讲解了。对于其他XML的导入，大家可以自己看一下MSDN的介绍。

博客数据库升级手记（三）：标签库的导入

2012-08-26T23:05:02Z

上篇文章留下了一个话题，关于标签库的导入。因为设计的变化，标签库无法从Keywords字段中直接导了。

首先，分析一下新表的结构。Tag表是用来保存所有标签的。Tag.Id是uniqueidentifier类型的主键，它将被PostTag表关联，来描述多对多的结构。Tag.Name是标签名称。这是可以从老数据的Blog.Keywords字段中获得的。

所以，第一步，是要从Keywords字段提取信息，插入到Tag表中。随后再处理关联问题。

老数据库里的Keywords字段中保存的，是以英文逗号分隔的文章标签。比如“ASP.NET,CSS,HTML”就表示这篇文章拥有3个标签，分别是ASP.NET、CSS和HTML。首先，我需要解析原先的Keywords字符串，将以逗号分隔的内容解析出来。但SQL Server没有提供Split函数，所以需要自己写一个：

CREATE FUNCTION [dbo].[udf_Split]
(
	@SourceSql   VARCHAR(8000),
	@StrSeprate  VARCHAR(10)
)
RETURNS @temp TABLE(a VARCHAR(100))
AS
BEGIN
	DECLARE @i INT
	SET @SourceSql = RTRIM(LTRIM(@SourceSql))
	SET @i = CHARINDEX(@StrSeprate, @SourceSql)
	WHILE @i >= 1
	BEGIN
	    INSERT @temp
	    VALUES
	      (
	        LEFT(@SourceSql, @i -1)
	      )
	    SET @SourceSql = SUBSTRING(@SourceSql, @i + 1, LEN(@SourceSql) -@i)
	    SET @i = CHARINDEX(@StrSeprate, @SourceSql)
	END
	IF @SourceSql <> '\'
	    INSERT @temp
	    VALUES
	      (
	        @SourceSql
	      )
	
	RETURN
END

这个函数可以很好的进行分割处理：

但旧数据库中的记录是一条条的keyword字符串，并不是完整拼接在一起的。而且由于T-SQL的语法特性，除非用游标（如此高级的东西我还没用过），不然是不能直接像C#那样foreach循环处理的。因此，我需要先把所有的keywords的筛选结果都拼成一个字符串：

DECLARE @allKeywordString NVARCHAR(MAX)
DECLARE @sql AS VARCHAR(8000)
SELECT @sql = ''
SELECT @sql = @sql + Keyword + ','
FROM   BlogSQL.dbo.Blog

SELECT @allKeywordString = @sql

这段代码的执行结果如下：

但有个小小的瑕疵，即最后一个Keyword之后会有一个逗号“,”。这个后面要记得处理掉。最后，插入Tag表的脚本如下：

INSERT INTO Tag
  (
    NAME
  )
SELECT DISTINCT a
FROM   dbo.udf_Split(@allKeywordString, ',')
ORDER BY a

DELETE 
FROM   Tag
WHERE  [Name] = ''

现在，Tag表里已经有数据了。下一步是要做文章和标签多对多的关联表PostTag。因为Post.Id已经和原来的Blog.Id关联不上了，所以现在定位文章的办法是按标题查找（不会有两篇文章标题一样）。找到以后，筛选它的keywords，使用udf_Split函数分割后，将该文章的Post.Id即它的标签对应的Id存入数据库（查询Tag表）。处理单篇文章的存储过程如下：

ALTER PROCEDURE [dbo].[MergePostTag](@PostId UNIQUEIDENTIFIER)
AS
BEGIN
	INSERT INTO PostTag
	  (
	    PostId,
	    TagId
	  )
	SELECT @PostId,
	       t.Id
	FROM   Tag t
	WHERE  t.Name IN (SELECT DISTINCT a
	                  FROM   dbo.udf_Split(
	                             (
	                                 SELECT b.Keyword
	                                 FROM   BlogSQL.dbo.Blog b
	                                        INNER JOIN EdiBlog.dbo.Post p
	                                             ON  p.Title = b.Title
	                                 WHERE  p.Id = @PostId
	                             ),
	                             ','
	                         ))
END
GO

接下来要对所有文章的Id都进行导入操作。但这次不能用SQL。如果这样写，是要爆的：

这仍然是由于T-SQL的语法限制。不可以将一个函数或存储过程的返回结果放在SELECT,IN等语句当中。尽管这看上去是一种自然的表达式。关于这个问题可以参考：http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure 但我觉得拼接字符串不是我能接受的解决办法。

所以，我码了个console程序用来批量导数据：

static void Main(string[] args)
{
    Console.WriteLine("Inserting Data to PostTag, please fuck waiting.");

    using (EdiBlogEntities context = new EdiBlogEntities())
    {
        int successCount = 0;
        int fuckedCount = 0;
        foreach (var item in context.Post.Select(p => p.Id))
        {
            if(context.MergePostTag(item) > 0)
            {
                successCount++;
                Console.WriteLine("Successfully merge tag with postId " + item.ToString());
            }
            else
            {
                fuckedCount++;
                Console.WriteLine("Merge tag fucked with postId " + item.ToString());
            }
        }

        Console.WriteLine("Work complete, {0} success, {1} fucked", successCount, fuckedCount);
    }
}

运行结果如下：

至此，标签库数据导入完成。下一篇将介绍XML数据的导入。

博客数据库升级手记（二）：新数据库的建立及简单数据的导入

2012-08-26T01:57:35Z

前面一片文章讲了如何将Access数据库直接导入SQL Server。但导入完成后，是强烈建议不要使用的。因为里面的object命名都与sql默认的不符，许多T-SQL行为也很诡异，所以我需要重新建立一个数据库，并且把旧的数据导入到新的数据库中。

新数据库的设计

对于新的数据库，这恰好是一次重构和升级的机会，我可以将4年前不合理的表结构设计重构一下（4年前也就是我第一次做这个博客的时候，菜鸟一只）。新的数据库中，博客子系统的设计如下（其实还有很多别的表，但不是给博客主业务用的，以后再讨论）：

而旧数据库仅仅只有3张表：Blog、Category、Comment。

对于Category，没有太大的改动，仅仅是增加一个”Name”字段，用来做MVC的路由，而”DisplayName”的功能则和原先数据库是一样的，表示这个分类在页面上显示的名称。

Comment表增加了Email字段，是可选的，用来保存评论者的邮件地址，以便我和他联系。

Blog表重命名为了Post，这是因为概念的转变。Blog应当是系统的总称，里面可以有文章、有留言、以及友情链接、设置等其他数据。而Post，则是描述所有的文章，是博客的主业务对象。

新增的表是Tag、PostTag及BlogAttachment。原先文章的标签都是在Blog.Keywords中以逗号分隔的，由网站程序负责检索和分割。这样显然不符合数据库设计规范（1NF），并且很影响效率。因此我单独抽了两张表，Tag用于保存标签名称。PostTag用于做多对多的关联（一篇文章可以对应很多个标签，而一个标签可以被许多文章使用）。

BlogAttachment保存的是文章的附件。这个功能最早也是在数据库里的，但后来交给了XML管理，其实这样做不太合理，所以这次又把它重新放回数据库了。

如何导入数据

建立了新数据库以后，下一步就是要把数据从老的数据库导入到新数据库了。这里面会存在许多要解决的问题。其中一个就是表结构不匹配的问题。如果只是字段名不一样，可以直接在INSERT SELECT语句中替换，而如果结构不一样，就要进一步处理了。

导入的时候也要注意，要从主表开始导数据，再导从表的数据（外键约束很明显）。

在SQL Server中，要跨数据库访问很简单，只要在要查询的对象前面加上数据库名就可以了。比如SELECT * FROM ABC.dbo.Table1，就算这条语句是在DEF数据库执行的，只要你拥有对ABC数据库的访问权限，就可以查询成功。

首先，最容易处理的是分类表（Category），脚本如下：

INSERT INTO Category
  (
    NAME,
    DisplayName
  )
SELECT c.CategoryName,
       c.CategoryName
FROM   BlogSQL.dbo.Category c

这个脚本会把新表的Name和DisplayName都用旧表的CategoryName来填充，新表的主键Id是自从产生的GUID（这不影响Category的导入，但对于从表Post来说，就需要额外处理CategoryId外键）。而更改Name的工作很明显，需要手动完成（毕竟没有哪个数据源中保存了Name）。

接下来要处理的是最Post表。这里有几个问题需要处理。首先，CategoryId不再能直接导入了，因为数据类型从int变成了uniqueidentifier。但我们还是能根据Category的DisplayName找到对应的一个Category，因为DisplayName是不会重复的，没有两个相同的分类。然后再取他的Id。这只需要两句Inner Join就搞定。

INNER JOIN BlogSQL.dbo.Category c
            ON  c.CategoryId = b.CategoryId
INNER JOIN EdiBlog.dbo.Category c2
            ON  c2.DisplayName = c.CategoryName

另外，同样的，旧数据库中文章的Id为int类型，而新数据库中是uniqueidentifier，即GUID。单单插入数据是不影响的，GUID可以用NEWID()自动产生，但在Post的从表Comment中，就会多出一个额外需要处理的问题，即Comment的外键PostId也不能直接导入了。

最后一个问题是，Access数据库中没有GUID对应的类型，因此我保存的都是空GUID作为替代。这个只要在最后将空ID字符串替换成NULL就好了。导入Post的脚本如下：

INSERT INTO Post
  (
    Title,
    Slug,
    PubDate,
    PostContent,
    Author,
    CommentEnabled,
    AttachmentId,
    IsPublished,
    Hits,
    Rators,
    Rating,
    CategoryId
  )
SELECT b.Title,
       b.Slug,
       b.Posttime,
       b.[Content],
       b.Author,
       b.IfReply,
       b.AttachmentId,
       b.IsDraft,
       b.Hits,
       b.Rators,
       b.Rating,
       c2.Id
FROM   BlogSQL.dbo.Blog b
       INNER JOIN BlogSQL.dbo.Category c
            ON  c.CategoryId = b.CategoryId
       INNER JOIN EdiBlog.dbo.Category c2
            ON  c2.DisplayName = c.CategoryName

UPDATE EdiBlog.dbo.Post SET AttachmentId = NULL WHERE AttachmentId = '00000000-0000-0000-0000-000000000000'

下一个表，是Post的从表Comment。和之前处理类型外键一样，用两个Inner Join搞定：

INSERT INTO Comment
  (
    Username,
    Email,
    IPAddress,
    PubDate,
    CommentContent,
    IsPublished,
    PostId
  )
SELECT c.Replyer,
       NULL,
       c.IPAddress,
       c.Posttime,
       c.ReplyContent,
       '1',
       p.Id
FROM   BlogSQL.dbo.Comment c
       INNER JOIN BlogSQL.dbo.Blog b
            ON  b.Id = c.BlogId
       INNER JOIN EdiBlog.dbo.Post p
            ON  p.Title = b.Title

最后要处理的是标签库，即Tag和PostTag两张表。这是本次升级中最麻烦的导入，必须另起一片文章详细论述。所以这篇就不讨论标签库了。有兴趣的读者请留意下一篇文章。

新的存储过程

原来用Access的时候，所有的业务操作都是放在程序中的，很影响效率。我的博客是一个internet站点，需要面对大量的并发访问，这些request并不全是人类，还有搜索引擎爬虫及其他内容检索程序，因此效率很关键。而SQL Server的一大好处就是可以利用存储过程，直接把耗时的逻辑在数据库层面解决，因此我就利用了这一特性，将一些业务操作重写到了存储过程里。下面挑选一些介绍。

GetCategoryCountList

这个存储过程做的是统计每个分类下有多少文章。用到了GROUP BY以及COUNT。

CREATE PROCEDURE [dbo].[GetCategoryCountList]
AS
BEGIN
    SELECT [Category].Name,
           [Category].DisplayName,
           COUNT(Post.Id) AS PostCount
    FROM   [Category]
           LEFT JOIN [Post]
                ON  [Post].CategoryId = [Category].Id
    GROUP BY
           [Category].Name,
           [Category].DisplayName
END

执行结果如下：

GetTagCountList

和刚才类似，也是做统计的。它会返回每个标签对应了多少文章。

CREATE PROCEDURE [dbo].[GetTagCountList]
AS
BEGIN
    SELECT t.Name            AS TagName,
           COUNT(pt.PostId)  AS TagCount
    FROM   Tag                  t,
           PostTag              pt
    WHERE  t.Id = pt.TagId
    GROUP BY
           t.Name
END

效果如下：

GetMonthList

返回每年各月份所发表的文章数量。用于做归档页面。

CREATE PROCEDURE [dbo].[GetMonthList]
AS
BEGIN
    SELECT YEAR(p.PubDate)   AS QYear,
           MONTH(p.PubDate)  AS QMonth,
           COUNT(p.Id)       AS QCount
    FROM   Post                 p
    GROUP BY
           YEAR(p.PubDate),
           MONTH(p.PubDate)
END

效果如下：

另一些不重要的存储过程就不介绍了。下一篇将介绍标签库的数据导入。

博客数据库升级手记（一）：将Access数据库迁移到SQL Server

2012-08-13T05:02:53Z

本人使用的网站空间送了个200M的SQL Server2008 R2数据库，我不用白不用。所以上周花了很多时间把网站从Access和XML数据存储上迁移到了SQL Server数据库，顺便也升级了一下架构，目前博客子系统已基本完成迁移，最近将逐个迁移页面集系统、留言等模块。

在迁移的过程中，有许多值得记录的经验，在实际项目中非常实用，所以记录下来以备参考。

首先，我的迁移思路是：数据的迁移尽量不通过程序去做，应当采用SQL Server去实现。那么第一步我就得把Access数据库迁移到SQL Server上，这样才能在SQL Server中进行一致的管理和迁移到新的数据库上。

我原来的数据库是Access2007格式的（格式没有太大的关系，2003也可以这样操作）。在Access 2010中的操作方法如下：

1. 在Database Tools选项卡下选择“SQL Server”：

2. 在弹出的Upsizing Wizard中选择"Create new database"

3. 然后配置目标SQL Server的连接信息，这里我是本机，Windows身份验证。注意，最后一个文本框中输入的是导入到SQL Server中的数据库的名称，这个数据库必须在SQL Server里不存在。这个名称很重要，之后我转储数据会经常用到。

4. 选择你需要导入到SQL的表，可以选择单个，也可以选择全部。点击“>”箭头就可以。

5. 根据自己的需要，选择哪些信息要导入到SQL Server。注意，不要在table中添加time span，所以要将那个下拉框选为"No, never"

6. 最后一步，选择"No application changes"。因为我迁移到SQL Server后，打算用Entity Framework做数据访问层，所以程序必然要改，这个最后要手工处理的。

7. 接下来Access就会连接SQL Server完成导入，最后会出来一张报表，不用鸟他。

8. 打开SQL Server，我的数据库已经乖乖的躺在里面了。导入完成。

但大家要注意，这个被导入的数据库是非常特殊的，许多实现都和SQL默认的不一样，所以尽量不要使用它来直接作为生产环境数据库。（不信你可以将数据库导出为SQL脚本看看里面都是什么奇葩）。所以我们需要再创建一个新数据库用来转储。下一篇文章将会介绍新数据库的建立和数据转储。

Edi Wang

From Azure Functions to a Database Outbox: Simplifying Moonglade Email Notifications

Before: A Separate Azure Function Service

After: An In-App Database Outbox

Code Structure

Event Handlers Only Enqueue Work

The Outbox Table

Claiming Messages With A Lease

The Background Worker

Processing, Retry, And Dead Letter

Dependency Injection

Configuration

A Linux Docker Issue

Why This Design Fits Moonglade

Deprecating The Standalone Repository

Final Thoughts

Azure App Service Pitfalls: Why a PostgreSQL Migration Fails in Production

The Setup

Step 1: Test the Migration Locally First

Step 2: Production Migration

Step 3: Only Read-Only Validation in Production

Problem 1: Azure App Service "Connection Strings" Did Not Behave as Expected

Problem 2: The App Service Service Tag Was Not Enough

Lessons Learned

1. Test the database migration separately from the app migration

2. Use PostgreSQL COPY for real migrations

3. Do not assume App Service "Connection strings" map cleanly for Linux containers

4. Service tags can be misleading

5. Health checks are not enough if startup can fail softly

Final Result

PowerShell to Enable SQL Server Express Remote Access

Problem

Solution

Steps

PowerShell

Import Data to Azure Storage Table from SQL Server

Preparation

Export Data

For a few amount of data

For general amount of data

Import Data

SQL Server Export Data Tier Application via Command Line

Merge Data for N-N Relationship Tables in T-SQL

Background

Solution

Azure SQL数据库Web Tier爆了，如何迁移数据库

EntityFramework 6 SqlQuery Passes Nullable Type Arguments

EntityFramework中使用.Include()做饥饿加载可能产生的性能问题

图解：如何将SQL Azure数据库备份到本地SQL Server

图解：如何将SQL Server数据库迁移到SQL Azure

Performance tips for Entity Framework

How to Manually Upgrade Entity Framework 4.0 to 5.0

Step 1: Install Entity Framework 5.0 from NuGet

Step 2: Update EDMX

Step 3: Update the CUD method

给ASP.NET Membership增加权限（Rights）功能

一、为什么要给Memebership增加权限系统

二、数据库更改

三、示例代码：ASP.NET MVC3应用程序的代码更改

在Entity Framework中使用LINQ语句分页

一、页数计算

二、分页存储过程的原理

三、LINQ to SQL分页

四、在Entity Framework中对某表分页

Store and Read Images via SQL Server in ASP.NET MVC 3

SQL

Upload Image to Database

Read Image from byte[]

博客数据库升级手记（四）：XML数据的导入

博客数据库升级手记（三）：标签库的导入

博客数据库升级手记（二）：新数据库的建立及简单数据的导入

新数据库的设计

如何导入数据

新的存储过程

博客数据库升级手记（一）：将Access数据库迁移到SQL Server

2. Use PostgreSQL `COPY` for real migrations