Simplifying creating array column with Linq to SQL

Question

I need to create SQL table with unknown sized array of strings, integers or whatever. Best way to do this is to create separate table for it. Like this (pseudo-code follows, I have no idea if I'm using "primary key" correctly):

public class DataBase : DataContext
{
    public Table<UserAccount> users { get { return GetTable<UserAccount>(); } }
    public Table<UserAccount_roles> userroles { get { return GetTable<UserAccount_roles>(); } }
}

[Table]
public class UserAccount
{
    [Column(IsPrimaryKey = true)]
    public string username;
    [Column]
    public string password;
}

[Table]
public class UserAccount_roles
{
    [Column(IsPrimaryKey = true)]
    public string userName;
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int roleIdx;
    [Column]
    public int arrayValue;
}

However, this feels clumsy as UserAccount_roles could be just simple "List<> roles" inside UserAccount-class if I shouldn't be worrying of SQL-implementation.

I'd rather do something like this:

public class DataBase : DataContext
{
    public Table<UserAccount> users { get { return GetTable<UserAccount>(); } }
}

[Table]
public class UserAccount
{
    [Column(IsPrimaryKey = true)]
    public string username;
    [Column]
    public string password;
    [MyColumn]
    public MySQLList<DataBase, int> roles;
}

I thought of doing "class MySQLList<B, T> : List<T>". In DataBase-class ("B") I have made some static methods to grant access to correct database. Then inside MySQLList I manipulate database by overloaded List<> -methods. This should result building and accessing table UserAccount_roles "under the hood", as programmer (mostly me) sees just simplified solution in form on customized List<>.

My actual questions are:

I bet there are some ready implementations for this. Where I can find one?
I think I am not able to do any "[table] class MyTable" -declarations in MySQLList<>. Do I have to do actual SQL-language coding there, or is there any way of creating Linq to SQL -class and attribute stuff during compilation / run-time?
Any generic caveats at this approach?-)

p.s.w.g · Accepted Answer · 2013-04-09 16:43:01Z

1

If you want to avoid having to explicitly declare junction tables, Entity Framework is probably better for you. It will allow you to directly create many-to-many relationships without needing an intermediate type.

Further Reading

The recommended way of doing this is in Linq-To-SQL to create two tables with a many-to-many relationship:

[Table]
public class UserAccount
{
    [Column(IsPrimaryKey = true)]
    public string username;
    [Column]
    public string password;

    private EntitySet<UserRole> _userRole;

    [Association(Storage = "_userRole", OtherKey = "userName")]
    public EntitySet<UserRole> UserRoles
    {
        get { return this._userRole; }
        set { this._userRole.Assign(value); }
    }
}

[Table]
public class Role
{
    [Column(IsPrimaryKey = true)]
    public int roleId;
    [Column]
    public string roleName;

    private EntitySet<UserRole> _userRole;

    [Association(Storage = "_userRole", OtherKey = "roleId")]
    public EntitySet<UserRole> UserRoles
    {
        get { return this._userRole; }
        set { this._userRole.Assign(value); }
    }
}

[Table]
public class UserRole
{
    [Column(IsPrimaryKey = true)]
    public int roleId;
    [Column(IsPrimaryKey = true)]
    public string userName;

    private EntityRef<Role> _role;

    [Association(Storage = "_role", ThisKey = "userName")]
    public EntitySet<Role> Role
    {
        get { return this._role.Entity; }
        set { this._role.Assign(value); }
    }

    private EntityRef<UserAccount> _userAccount;

    [Association(Storage = "_userAccount", ThisKey = "userName")]
    public EntitySet<UserRole> UserAccount
    {
        get { return this._userAccount.Entity; }
        set { this._userAccount.Assign(value); }
    }
}

This is far better than having a serialized array of integers that must be parsed and processed in C#. You can easily do things like:

var userRoleIds = myUser.UserRoles.Select(ur => ur.roleId);

But you can also do much more that would be impossible with a serialized array.

Further Reading

How to: Map Database Relationships

If you really want to store an array in a single column, I'm afraid there's no standard way of doing this in Linq-to-SQL (possibly some third-party providers will support it). Your best bet is to do the serialization yourself, like so:

[Table]
public class UserAccount
{
    ...
    [Column]
    private string rawRoles;

    public IEnumerable<int> Roles
    {
        get 
        {
            return (this.rawRoles == null) 
                ? return new int[0] : 
                : this.rawRoles.Split(",").Select(s => int.Parse(s));
        }
        set 
        {
            this.rawRoles = (value == null)
                ? null
                : String.Join(",", value);
        }
    }
}

Of course, there are better ways of serializing your data, this is just an example. You can't use this in any Linq-to-SQL query, and it's very poor database design - but hey, at least it satisfies your requirements.

edited Apr 9, 2013 at 16:43

answered Apr 7, 2013 at 13:42

p.s.w.g

150k31 gold badges307 silver badges339 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Stumbler Over a year ago

Using EntitySet and EntityRef is a good advice and I take it. However, my main problem is to avoid having to define one or two additional tables every time I want to create a simple list of one-way data to my table. How I handle data itself is irrelevant. I'm currently more interested about how can I create generic solution that hides Role and UserRole -classes as unnecessary information. As I know that they are just ways to store array of X, I want to generate them automatically under the hood.

p.s.w.g Over a year ago

@user2254053 Why would you want to avoid creating table? Tables are exactly what your database is for. The recommended solution is much more flexible, stable, and reliable -- it's just good database design (sure, there may be a handful of cases where it's warranted, but those are few and far between). But, I've included an alternate solution for completeness.

Stumbler Over a year ago

I don't want to avoid creating table. Quite opposite. I just want to streamline it a little bit to avoid copy-paste every time I need an array of data. And, of course, to add code readability by hiding tables whose only purpose is to represent array of values inside some other table. String parsing could do it, but like you said, it is horrible and limiting solution.

p.s.w.g Over a year ago

@user2254053 I see. Well, Linq-to-SQL isn't the best in terms of brevity. AFAIK you're kind of stuck with this boilerplate code. If you're designing an DAL for others to consume and you want to keep the API clean, you could make the types internal and provide wrapper properties / extension methods to access the kind of information you want. Entity framework gives you the power to specify many-to-many relationships directly without explicitly specifying a junction table, and EF 5 supports enums which would be better if you have a fixed set of values you need to represent.

Stumbler Over a year ago

Entity Framework seems to be a very good match with my question number 1. It also seems to be most advisable solution over Linq-to-SQL now that I did some research of both. Do you want to change your answer accordingly so I can "like" it, or should do I make my own answer?

|

Collectives™ on Stack Overflow

Simplifying creating array column with Linq to SQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related