Query Membership using Linq

Question

Hi,

I am not a experienced programmer, I need to query the Membership User Collection provided in asp.net mvc.

I want the members be able to add other members as friends, I have created a added friend table.

Id, MemberId, Friend_MemberId, DateAdded

I want to display a list of Members which are not added to this list (like filter already existing friends), but unable to query using linq, can anyone suggest a way, links, articles, would it be better to extend memebership class.

Answer 1

There are quite a number of ways you could go about this.

Let's examine one.

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

Stipulations:

• You are using default default SqlProviders
• You know how to add an ADO.Net Entity Model using the ASPNETDB
• Your ASPNETDB serves one application, the default '/'. If this is not the case then you will already have the necessary knowledge to adjust the following guidance.

Create the Friends table in the ASPNETDB:

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

• Select your project in Solution Explorer, click 'show all files' icon at the top of Solution Explorer, expand '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

• Paste 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 an ADO.Net Entity Data Model to your project and include, at the minimum, the following:

Tables

• Friends

Views

• vw_aspnet_MembershipUsers

Query Example:

NOTE: I am by no means a Linq guru. These queries work fine and the generated sql does not seem unreasonable to me, but I am sure there is someone who will have helpful suggestions regarding possible optimizations of the queries.

// 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