Need Help on LINQ-Query

I have 3 Tables:

Table User (UserID, Username)
Table Admin (AdminID, Username)
Table PM (PMID, SenderID, Sendertype, RecipientID, RecipientType)

Now I will, in 1 Query:

• Get all Information from PM
• if SenderType == 'A' join SenderID to Admin-Table, get Username
• if SenderType == 'U' join SenderID to User-Table, get Username
• if RecipientType == 'A' join RecipientID to Admin-Table, get Username
• if RecipientType == 'U' join RecipientID to User-Table, Get Username

someone have an Idea how to solve in 1 query?

Why not just have a single user table that instead has a user type of either User or Admin, you could save yourself a lot of trouble... LINQ queries translate directly to SQL, so I'm not sure if you can do an indeterminate join like that in just one query.

Alternatively, if you don't want to modify the tables you could also create a view that does basically the same thing.

Or, write a stored procedure.

If you really want to use LINQ, you'll probably have to do more than one query. You could union the two User/Admin tables with a third field to differentiate, and then join to your PM table using that dataset instead.

try this solution

var q = from pm in PM
        select new {
            pm.PMID,
            pm.SenderID,
            pm.Sendertype,
            pm.RecipientID,
            pm.RecipientType,
            Username = pm.SenderType == "A" ? pm.Admin.Username : 
                        pm.SenderType == "U" ? pm.User.Username : "",
            RecipientType = pm.RecipientType == "A" ? pm.Admin.Username : 
                        pm.RecipientType == "U" ? pm.User.Username : ""
        }

As I always recommend, break out LinqPad and play. Unless you're doing a pivot or a bulk insert statement, there's almost nothing you can do in T-SQL that you can't do in Linq.

The trick on this one is to make a union query between your Admin and User tables. After that, it comes together easily. While I broke it into two statements, that was for clarity - there's only one query actually executed. If you're desperate for one statement, you can easily do that from here. So here's your code (verified in LinqPad):

var combined = Admins.Select(a => new {ID = a.AdminID, UserType = "A", Username = a.Username}).Union(
               Users.Select(u => new {ID = u.UserID, UserType = "U", Username = u.Username}));

var result = from p in PMs
             join sendr in combined on new {ID = p.SenderID, UserType = p.Sendertype} equals new {sendr.ID, sendr.UserType}
             join recpt in combined on new {ID = p.RecipientID, UserType = p.RecipientType} equals new {recpt.ID, recpt.UserType}
             select new {p, sendr, recpt}; // pretty this anon-type up if desired...
result.Dump();