Limitations of Entity Framework support with MySQL 6.4.3 Connector/Net

I started learning the Entity Framework so I can add support for MySQL side-by-side MSSQL in FaultTrack Professional (issue tracking software I am developing), and I started to run into quite a few limitations with MySQL.

#1    DDL Generation (SSDLToMySQL.tt (VS))
The MySQL DDL Generation Template has some shortcomings. The most obvious that I ran into was max-length string fields. I had a field named 'StackTrace' that in MSSQL is nvarchar(MAX). Now in MySQL, the equivelent should simply be varchar(65535), but if you try to run a script to create a field you will get an error. This means that when you generate your DDL script that you have to go back and manually change it. This is because MySQL will actually expect you to use text instead of varchar(65535), and will throw an error.

#2 Guid (UNIQUEIDENTIFIER) Support
UUID() was introduced in later versions of MySQL, and moreso than that you MySQL now treats CHAR(36), BINARY(16) as guid type. The problem with this is when you start using this in your entity lambda expressions, you start to get errors thrown. Take this code example:

///<summary>
/// Returns the end result permission for the specified account and the associated Team Project. This resolves all inherited and override permissions
/// </summary>
public AccountPermissions GetTeamProjectPermission(System.Guid accountID, int collectionID, int projectID) {
    using (FaultTrackObjectContext context = new FaultTrackObjectContext(Settings.EntityConnectionString)) {
        Account account = context.Accounts.Single(a => a.ID == accountID);
        if (account.Role == AccountRoles.Administrator.ToString()) {
            return AccountPermissions.Grant;
        }
        else if (account.Role == AccountRoles.ProjectManager.ToString()) {
            return GetTeamProjectProjectManagerPermission(context, account, collectionID, projectID);
        }
        else if (account.Role == AccountRoles.Developer.ToString()) {
            return GetTeamProjectDeveloperPermission(context, account, collectionID, projectID);
        }
    }
    return AccountPermissions.Revoke;
}

Specifically notice the lambda expression a => a.ID == accountID. This line actually will throw a InvalidOperationException, "The sequence contains no elements". I know I had exactly one matching element in the database, so I did a little debugging:

Sure enough, what I found was by expanding the results view, that I did indeed have exactly one matching element. So what the heck? The lambda expression is failing; a => a.ID == accountID resolves to false and no matching elements are found. This is because of the way that MySQL stores the Guid. Even though your DDL looks good, your SSDL mappings look great, and your code looks even better, under-the-hood the provider implementation for Guid handling is not properly done. I've not looked at it myself, but some other devs I have spoken with have talked about how the connector uses ConvertTo and the conversion fails. Whatever the case may be under-the-hood in the connector, it simply does not work.

What I ended up doing, which was a benefit to my software anyway, was swapping all the primary keys from Guid types to integer types. The bottom line is, Entity has a lot of shortcomings, and it works best with primitive types. Actually, the issue with primitive types deserves its own explanation.

#3 Primitive Types
I want you to take a look at this code.

private bool PermissionConflicts() {
    bool conflicts = false;
    /* required for entity queries */ int accountID = (uxAccounts.SelectedItem as Account).ID;
    /* required for entity queries */ int permission = (int)(AccountRoles)uxPermission.SelectedItem;
    /* required for entity queries */ int grantPermission = (int)AccountPermissions.Grant;
    /* required for entity queries */ int readPermission = (int)AccountPermissions.Read;
    /* required for entity queries */ int revokePermission = (int)AccountPermissions.Revoke;
    switch (m_PermissionType) {
        case PermissionType.TeamCollection:
            if (Context.TeamCollectionPermissions.Any(predicate => predicate.AccountID == accountID
                && permission == grantPermission
                && predicate.TeamCollectionID == m_TeamCollection.ID)) {
                    if (permission == revokePermission || permission == readPermission) {
                        conflicts = true;
                    }
            }
            if (Context.TeamCollectionPermissions.Any(predicate => predicate.AccountID == accountID
                && permission == readPermission
                && predicate.TeamCollectionID == m_TeamCollection.ID)) {
                if (permission == grantPermission || permission == revokePermission) {
                    conflicts = true;
                }
            }
            if (Context.TeamCollectionPermissions.Any(predicate => predicate.AccountID == accountID
                && permission == revokePermission
                && predicate.TeamCollectionID == m_TeamCollection.ID)) {
                    if (permission == grantPermission || permission == readPermission) {
                    conflicts = true;
                }
            }
            break;
        case PermissionType.TeamProject:
            if (Context.TeamProjectPermissions.Any(predicate => predicate.AccountID == accountID
                && permission == grantPermission
                && predicate.TeamProjectID == m_TeamProject.ID)) {
                    if (permission == revokePermission || permission == readPermission) {
                        conflicts = true;
                    }
            }
            if (Context.TeamProjectPermissions.Any(predicate => predicate.AccountID == accountID
                && permission == readPermission
                && predicate.TeamProjectID == m_TeamProject.ID)) {
                if (permission == grantPermission || permission == revokePermission) {
                    conflicts = true;
                }
            }
            if (Context.TeamProjectPermissions.Any(predicate => predicate.AccountID == accountID
                && permission == revokePermission
                && predicate.TeamProjectID == m_TeamProject.ID)) {
                    if (permission == grantPermission || permission == readPermission) {
                    conflicts = true;
                }
            }
            break;
    }
    return conflicts;
}

Specifically, this:

predicate =>
       predicate.AccountID == accountID
    && permission == grantPermission
    && predicate.TeamCollectionID == m_TeamCollection.ID

In this lambda expression, we are operating on primitive types. This is fine, but when you are dealing with non-primitive types, the provider (not just MySQL's provider), cannot properly convert the non-primitive types into primitive types, and the conversion fails, thus throwing an exception.

Prior to moving to Entity when the software was built on DLinq (Linq to SQL), the lamda was easily expressible:

predicate =>
    predicate.Account == uxAccounts.SelectedItem as Account
    && (AccountPermissions)predicate.Permission == AccountPermissions.Grant
    && predicate.TeamCollectionID == m_TeamCollection.ID

Unfortunately you cannot do that with Entity (hopefully I can use the term 'yet'). I may write more on the Entity Framework; I am still learning it and it has a lot of potential. Most everything seems to have some workaround, but these were some of the annoyances I ran into that made me think twice about adding support for MySQL. Not everyone has access to an MSSQL Engine and MySQL is the other popular choice among others, so it really is a must.