Multimapping von verschachtelten List-Objekten via Dapper

Guten Tag zusammen,

bei der Frage gehts mir um SQL-Abfragen via Dapper:
Ich habe drei Models User, Orders, Projects, welche jeweils in einer Tabelle abgelegt sind. Orders und Projects haben mit Users eine n:n-Relation welche über zwei Crosstables UserOrders und UserProjects realisiert wird:

public class User
{
  public string UserID {get;set;}
  public List<string> Orders{get;set;}
  public List<string> Projects {get;set;}
}

public class Order
{
  public string OrderID {get;set}
  ...
}

public class Project
{
  public string ProjectID {get;set}
  ...
}

In den beiden Listen des Usermodels sind jeweils die IDs der Orders/Projects enthalten. Und ich möchte nun mit einem Query die Liste aller Users erhalten.

Ich habe diese Lösung die auch wunderbar für eine verschachtelte Liste funktioniert:

var lookup = new Dictionary<string, User>();
var multi = dbDapperFM.Query<User, string, string, User>("SELECT u.*, uo.OrderID, up.ProjectID "+
        "FROM User u INNER JOIN UserOrders uo ON u.UserID=uo.UserID "+
        "INNER JOIN UserProjects up ON u.UserID=up.UserID", (u, uo, up) =>
    {
      User user;
      if (!lookup.TryGetValue(m.UserID, out user))
          lookup.Add(u.UserID, user= u);

      if (user.Orders == null)
          user.Orders = new List<string>();
      user.Orders.Add(uo);

      if (user.Projects == null)
          user.Projects = new List<string>();
      user.Projects.Add(up);
      return user;
    }, splitOn: "UserID , OrderID, ProjectID ").AsQueryable();

Für Orders kommen hier immer korrekte Ergebnisse. Für Projects kriege ich aber wenn ich x Orders und y Projects habe x*y Ergebnisse zurück, was auch völlig logisch ist, da durch die Inner Joins ja genau dieses Ergebnis generiere.
Nur hatte ich gehofft, dass das man durch entsprechendes Mapping abfangen kann.

Ich habe bereits eine Lösung in der ich zunächst alle User, dann alle UserOrders und UserProjects abfrage und das "per Hand" über Linq zusammenführe. Also mit insgesamt 3 Queries.

Meine Frage ist nun, ob es eine Möglichkeit gibt dies komplett in einem Query umzusetzen.

Beste Grüße
emuuu

Hi,

ist zugegebenermaßen etwas frickelig - aber folgendermaßen mache ich das:
(nur als Beispiel)

ublic override IEnumerable<CompoundIdentityResource> GetAllCompound()
		{
			var command = $"SELECT * FROM {TableName} AS iRes" +
			              $" LEFT JOIN {UnitOfWork.DapperDataService.NameService.NameByType(typeof(IdentityResourceClaimEntity))} AS iClaim" +
			              $" ON iRes.{nameof(IdentityResourceEntity.Id)} = iClaim.{nameof(IdentityResourceClaimEntity.IdentityResourceId)}" +
			              $" LEFT JOIN {UnitOfWork.DapperDataService.NameService.NameByType(typeof(IdentityResourceScopeEntity))} AS iScope" +
			              $" ON iRes.{nameof(IdentityResourceEntity.Id)} = iScope.{nameof(IdentityResourceScopeEntity.IdentityResourceId)}" +
			              $" LEFT JOIN {UnitOfWork.DapperDataService.NameService.NameByType(typeof(ScopeEntity))} AS scope" +
			              $" ON iScope.{nameof(IdentityResourceScopeEntity.ScopeId)} = scope.{nameof(ScopeEntity.Id)}";
			var lookup = new Dictionary<int, CompoundIdentityResource>();
			UnitOfWork.Connection
				.Query<IdentityResourceEntity, IdentityResourceClaimEntity, IdentityResourceScopeEntity, ScopeEntity,
					CompoundIdentityResource>(command,
					(entity, identityClaim, identityScope, scope) =>
					{
						// make sure the pk exists
						if (entity == null || entity.Id == default(int))
							return null;

						// make sure our list contains the pk
						if (!lookup.ContainsKey(entity.Id))
							lookup.Add(entity.Id, new CompoundIdentityResource
							{
								IdentityResource = entity
							});

						// fetch the real element
						var tempElem = lookup[entity.Id];

						// add identity-scope
						if (identityScope != null)
							tempElem.IdentityResourceScopes.Add(identityScope);

						// add claim
						if (identityClaim != null)
							tempElem.IdentityResourceClaims.Add(identityClaim);

						// add scope
						if (scope != null)
							tempElem.Scopes.Add(scope);

						return tempElem;
					}, param: null, transaction: UnitOfWork.Transaction);
			return lookup.Values;
		}

LG