Query Membership Using Linq

There are many ways to do this.

Let's take a look at one.

You can download a working VS2008 solution here . The example is not an MVC project, but the membership provider works the same regardless.

TERMS:

• You are using the standard SqlProviders by default
• Do you know how to add ADO.Net Entity Model using ASPNETDB
• Your ASPNETDB serves one default '/' application. If this is not the case, then you already have the necessary knowledge to set up the following recommendations.

Create a friends table in ASPNETDB:

The following assumes that you are using the default ASPNETDB created in app_data. If not, then you have already created and connected to another DB, just take what you need.

• Select the project in Solution Explorer, click the Show All Files icon at the top of Solution Explorer, expand the App_Data folder, and right-click> Open ASPNETDB.MDF.

• In Server Explorer, you will see your ASPNETDB.

• Project> Add New Item> Text File> Friends.sql

• Insert your query below. Save.

• Right click in editor> Connection> Connect> select ASPNETDB

• Right click in editor> Execute SQL

Friends.sql

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Friends
    (
    Id int NOT NULL IDENTITY (1, 1),
    MemberId uniqueidentifier NOT NULL,
    Friend_MemberId uniqueidentifier NOT NULL,
    DateAdded datetime NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
    DF_Table1_DateAdded DEFAULT GetDate() FOR DateAdded
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
    PK_Table1 PRIMARY KEY CLUSTERED 
    (
    Id
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
    IX_Table1 UNIQUE NONCLUSTERED 
    (
    MemberId,
    Friend_MemberId
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Table1 SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

      

        
        
        
      

    

Add the Entity Data ADO.Net data model to your project and include at least the following:

Tables

• Friends

Views

• vw_aspnet_MembershipUsers

Example request:

NOTE. I am by no means a Linq guru. These queries work fine and the generated sql doesn't seem unreasonable to me, but I'm sure there is someone out there who will have helpful suggestions on possible query optimizations.

// there are 3 users: User1, User2 and User3

// User1 has one friend, User2

string username = "User1"; // this would be the User.Identity.Name (currently logged in user)

// 

vw_aspnet_MembershipUsers[] friends;
vw_aspnet_MembershipUsers[] notFriends;

using (var ctx = new ASPNETDBEntities())
{
    // get the userId
    Guid userId = ctx.vw_aspnet_MembershipUsers.First(m => m.UserName == username).UserId;




    var usersFriendsQuery = from friend in ctx.Friends
                            join muser in ctx.vw_aspnet_MembershipUsers on friend.Friend_MemberId equals muser.UserId
                            where friend.MemberId == userId
                            select muser;


    friends = usersFriendsQuery.ToArray();

    Debug.Assert(friends.Count()==1);
    Debug.Assert(friends[0].UserName=="User2");



    var usersNotFriendsQuery = from muser in ctx.vw_aspnet_MembershipUsers
                               where ctx.vw_aspnet_MembershipUsers.Any(m =>
                                   ctx.Friends.FirstOrDefault(friend =>
                                       // include self in excluded members
                                       (muser.UserId == userId)
                                       ||
                                           // include already friends in excluded members
                                       (friend.MemberId == userId && friend.Friend_MemberId == muser.UserId)
                                       ) == null)
                               select muser;


    notFriends = usersNotFriendsQuery.ToArray();

    Debug.Assert(notFriends.Count() == 1);
    Debug.Assert(notFriends[0].UserName == "User3");
}

// do something interesting with friends and notFriends here