C# and ADO .NET entity data models with LINQ associations and entities for the same table

Question

My database tables are as follows: Users (UserId, Name) Friends (UserIdFrom, UserIdTo)

I also have an ADO .NET entity data model that describes these tables.

My .NET entity data model has an entity named Users and an association for that entity called Friends that corresponds to the Friends table.

My question is that I want to get a LINQ statement to select all of the association objects (Note: A can be a friend of B but B not a friend of A).

In the .NET entity data model, I tried adding a new entity for Friends but it says I can't use the same name for the same table as the association on the User table.

That way I would be able to do this:

var friendsSetResults = from friends in context.Friends where friends.UserIDFrom == userID || friends.UserIDTo == userID select friends;

But as I said it won't allow me to both create an association plus an entity for this table.

If I was using SQL directly it would be a simple statement, but I'm stuck with LINQ. What are my options?

My goal is to delete each of the entries both from and to.

Answer 1

If you want to delete a set of data, you are better of sending the delete query to the db directly, so you can do a single delete from friends where userIdFrom = @userId || userIdTo = @userId.

I don't know in the entity framework, but on linq2sql you can add a method in the partial class of the context and have it call a query - like context.deleteFriends(userId). There must be something similar in the entity framework.

If you want to do it through the entity framework, u can:

var user = context.Users.First(u=>u.userID == userId);
var toFriends = user.ToFriends.ToList();
var fromFriends = user.FromFriends.ToList();
foreach(var u in ToFriends) {
   user.ToFriends.Remove(u); //pseucode --- use whatever syntax that allows you to remove in entity framework
}
foreach(var u in FromFriends) {
   user.FromFriends.Remove(u); //pseucode --- use whatever syntax that allows you to remove in entity framework
}
context.SubmitChanges();