Where is |DataDirectory| defined?

Solution 1:

The |DataDirectory| isn't a file per se. A quote from a rather old MSDN article:

By default, the |DataDirectory| variable will be expanded as follow:

• For applications placed in a directory on the user machine, this will be the app's (.exe) folder.
• For apps running under ClickOnce, this will be a special data folder created by ClickOnce
• For Web apps, this will be the App_Data folder

Under the hood, the value for |DataDirectory| simply comes from a property on the app domain. It is possible to change that value and override the default behavior by doing this:

AppDomain.CurrentDomain.SetData("DataDirectory", newpath)

A further quote regarding your schema inconsistencies:

One of the things to know when working with local database files is that they are treated as any other content files. For desktop projects, it means that by default, the database file will be copied to the output folder (aka bin) each time the project is built. After F5, here's what it would look like on disk

 MyProject\Data.mdf

 MyProject\MyApp.vb

 MyProject\Bin\Debug\Data.mdf

 MyProject\Bin\Debug\MyApp.exe

At design-time, MyProject\Data.mdf is used by the data tools. At run-time, the app will be using the database under the output folder. As a result of the copy, many people have the impression that the app did not save the data to the database file. In fact, this is simply because there are two copies of the data file involved. Same applies when looking at the schema/data through the database explorer. The tools are using the copy in the project, not the one in the bin folder.

Solution 2:

The |datadirectory| algorithm is located in the System.Data.dll assembly, in the internal System.Data.Common.DbConnectionOptions class. Here it as displayed by ILSpy (note the source it's now available in the reference source repository: https://github.com/Microsoft/referencesource/blob/e458f8df6ded689323d4bd1a2a725ad32668aaec/System.Data.Entity/System/Data/EntityClient/DbConnectionOptions.cs):

internal static string ExpandDataDirectory(string keyword,
                                           string value,
                                           ref string datadir)
{
    string text = null;
    if (value != null && 
        value.StartsWith("|datadirectory|", StringComparison.OrdinalIgnoreCase))
    {
        string text2 = datadir;
        if (text2 == null)
        {
            // 1st step!
            object data = AppDomain.CurrentDomain.GetData("DataDirectory");
            text2 = (data as string);
            if (data != null && text2 == null)
                throw ADP.InvalidDataDirectory();

            if (ADP.IsEmpty(text2))
            {
                // 2nd step!
                text2 = AppDomain.CurrentDomain.BaseDirectory;
            }
            if (text2 == null)
            {
                text2 = "";
            }
            datadir = text2;
        }

        // 3rd step, checks and normalize
        int length = "|datadirectory|".Length;
        bool flag = 0 < text2.Length && text2[text2.Length - 1] == '\\';
        bool flag2 = length < value.Length && value[length] == '\\';
        if (!flag && !flag2)
        {
            text = text2 + '\\' + value.Substring(length);
        }
        else
        {
            if (flag && flag2)
            {
                text = text2 + value.Substring(length + 1);
            }
            else
            {
                text = text2 + value.Substring(length);
            }
        }
        if (!ADP.GetFullPath(text).StartsWith(text2, StringComparison.Ordinal))
            throw ADP.InvalidConnectionOptionValue(keyword);
    }
    return text;
}

So it looks in the current AppDomain data first (by default, there is no "DataDirectory" data defined I believe) and then gets to the current AppDomain base directory. The rest is mostly checks for path roots and paths normalization.

Solution 3:

On the MSDN forum there is a similiar but simplified question about this, which says:

By default the |DataDirectory| points to your application folder (as you figured out yourself in the original question: to the App_Data).

Since is just a substitution path to your database, you can define the path yourself with the AppDomain.SetData.