ASP.NET Membership and Linq to SQL

Question

I am wondering the best way about setting up asp.net membership in sql server to work well with linq to sql.

I'd like to use the username field for the foreign key relationships and have linq to sql recognise the asp user property.

I am having some problems getting relationships right between the users table and my other tables. Do I need to set username to a primary key for this to work?

Answer 1

The primary key constraint on Users database table has nothing (or little) to do with ASP.NET membership provider. Membership provider is a class implementing abstract class MembershipProvider defined in System.Web.Security. By default asp.net applications use membership provider linked to aspnet_XXX tables, but you can write your own provider that will use your own "Users" table and access it through linq2sql. Al you have to do is create class inheriting from System.Web.Security.MembershipProvider and set up it in web.config like:

<membership defaultProvider="MyMembershipProvider">
  <providers>
    <clear/>
    <add name="MyMembershipProvider" type="MyNamespace.MyMembershipProvider" applicationName="MyApp"/>
  </providers>
</membership>

The methods you have to implement use string username to identify users, but it doesn't have to be primary key of your table. It should by just unique field. I think the good idea is to use INT as primary keys.

Answer 2

When I did it, I just let the UserId field in the aspnet_Users table be the foreign key. When you use LinqToSql you can still filter based on the UserName in asp like this:

from data in connection.MyTable
where data.aspnet_User.UserName == User.Identity.Name
select data;

This is because of the foreign key relationship between the aspnet_Users table and your own table.