LINQ Multiple Join FOR XML Lambda Query

Question

I'm trying to replicate the below SQL query in LINQ Lambda by using String.Join. Could someone please point out how I can correct Lambda query.

I have formatted my SQL and placed the error message here.

Here is the error I receive:

System.InvalidOperationException: The LINQ expression 'DbSet<TblObligor>()
    .Join(
        inner: DbSet<TblObligorGuaranties>(), 
        outerKeySelector: t => t.ObligorId, 
        innerKeySelector: t0 => (decimal)t0.ObligorID, 
        resultSelector: (t, t0) => new TransparentIdentifier<TblObligor, TblObligorGuaranties>(
            Outer = t, 
            Inner = t0
        ))
    .Join(
        inner: DbSet<TblObligorGuarantyTypes>(), 
        outerKeySelector: ti => ObligorService.MapObligorGuaranties(
            o: ti.Outer, 
            og: ti.Inner).GuarantyTypeID, 
        innerKeySelector: t1 => (Nullable<int>)t1.GuarantyTypeID, 
        resultSelector: (ti, t1) => new TransparentIdentifier<TransparentIdentifier<TblObligor, TblObligorGuaranties>, TblObligorGuarantyTypes>(
            Outer = ti, 
            Inner = t1
        ))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

I want a string concatenated colmn of GuarantyTypeDescription.

select 
  o2.ObligorID, 
  STUFF(
    (
      select 
        ',' + cast(
          ogt.GuarantyTypeDescription as nvarchar
        ) 
      from 
        tblObligor o 
        left join tblObligorGuaranties og on o.ObligorId = og.ObligorID 
        left join tblObligorGuarantyTypes ogt on og.GuarantyTypeID = ogt.GuarantyTypeID 
      where 
        1 = 1 
        and o.ObligorID = o2.ObligorID 
        and o.assetid = 1996323923 for xml path('')
    ), 
    1, 
    1, 
    ''
  ) as xmlstring 
from 
  tblObligor o2 
where 
  1 = 1 
  and o2.assetid = 1996323923

Here is my code:

public async Task<IEnumerable<ObligorGuarantyDTO>> GetObligorsListAsync(int? assetId)
{
    var obligorGuarantiesList = _context.TblObligor
       .Join(_context.TblObligorGuaranties, o => o.ObligorId, og => og.ObligorID, (o, og) => new { o, og })
       .Select(join => MapObligorGuaranties(join.o, join.og))
       .Join(_context.TblObligorGuarantyTypes, og => og.GuarantyTypeID, ogt => ogt.GuarantyTypeID, (og, ogt) => new { og, ogt })
       .Select(join => MapObligorGuarantyTypes(join.og, join.ogt))
       .AsEnumerable();

    return obligorGuarantiesList;
}

Here are my maps:

    private static ObligorGuarantyDTO MapObligorGuaranties(TblObligor o, TblObligorGuaranties og)
=> new ObligorGuarantyDTO()
{
    ObligorID = o.ObligorId,
    GuarantyID = og.GuarantyID,
    GuarantyTypeID = og.GuarantyTypeID,
    Release = og.Release,
    ReleaseDate= og.ReleaseDate,
    Note= og.Note,
    EditBy = og.EditBy,
    EditTime= og.EditTime
};

    private static ObligorGuarantyDTO MapObligorGuarantyTypes(ObligorGuarantyDTO og, TblObligorGuarantyTypes ogt)
    => new ObligorGuarantyDTO()
    {
    GuarantyTypeID = ogt.GuarantyTypeID,
    GuarantyTypeDescription = String.Join(", ", ogt.GuarantyTypeDescription)
    };

As a courtesy to those from whom you are asking help, I suggest you take a few minutes to add some indentation to your SQL. (Surely your final product doesn't look like that.) As posted, many will take one look and move on without trying to decipher it. — T N
– T N, Commented Feb 15, 2023 at 4:58
And please also include a description of what results you are getting now (syntax error, runtime error, unexpected results) plus your expected results for a given set of data. Oh, and include some sample data to match your expected results. — T N
– T N, Commented Feb 15, 2023 at 5:06
Your C# functions MapObligorGuaranties and MapObligorGuarantyTypes have no SQL server side equivalents, so entity framework cannot translate your query into SQL. Try to rewrite the lead part of your query without using custom C# functions, or write it so the they are applied only after the .AsEnumerable(). Note that only the portion of the query up to the .AsEnumerable() will be performed on the SQL server side and everything after will be performed in memory on the C# side, so be sure to include any data limiting .Where() conditions before the .AsEnumerable(). — T N
– T N, Commented Feb 15, 2023 at 14:18

T N · Accepted Answer · 2023-02-15 19:36:49Z

I'm a bit out of my element with multi-level joins in LINQ, but I think the following should put you on the right track.

As far as I know, there is nothing you can write in LINQ that will translate to the FOR-XML string concatenation SQL. Although recent versions of SQL Server now have a STRING_AGG() function, I'm not sure is there is a LINQ mapping for that either. (Someone may correct me if I am wrong.)

So the apparent best plan is to write a query to return a collection of ObligorID/GuarantyTypeDescription pairs, group by ObligorID, and then String.Join() the GuarantyTypeDescription values.

The first step is writing LINQ that will translate into the equivalent of the following SQL:

select o.ObligorID, ogt.GuarantyTypeDescription
from
  tblObligor o 
  left join tblObligorGuaranties og on o.ObligorId = og.ObligorID 
  left join tblObligorGuarantyTypes ogt on og.GuarantyTypeID = ogt.GuarantyTypeID 
where 1=1
  and o.assetid = 1996323923

Assuming that you are using a provider like Entity Framework that adds collection properties to your classes that represent foreign key relationships, I think the following may do the job:

_context.TblObligor
    .Where(o => o.assetid = 1996323923)
    .SelectMany(o => o.TblObligorGuaranties, (o, og) => new { o, og })
    .SelectMany(o2 => oog.TblObligorGuarantyTypes, (o2, ogt) => new { o2.o, o2.og, ogt })
    .Select(o3 = new { o3.o.ObligorID, o3.ogt.GuarantyTypeDescription })

If your classes don't have those collection properties, the following can be used (similar to what you had, but without the mapping classes):

_context.TblObligor
    .Where(o => o.assetid = 1996323923)
    .Join(_context.TblObligorGuaranties, o => o.ObligorId, og => og.ObligorId,
            (o, og) => new { o, og })
    .Join(_context.TblObligorGuarantyTypes, o2 => o2.o.ObligorId, ogt => og.ObligorId,
            (o2, ogt) => new { o2.o, o2.og, ogt })
    .Select(o3 = new { o3.o.ObligorID, o3.ogt.GuarantyTypeDescription })

At this point we need to switch to IEnumerable<> to to the grouping and concatenation in memory:

If you care about order, you may need apply an .OrderBy() before the .Select().

    .AsEnumerable()
    .GroupBy(x => x.ObligorID)
    .Select(g => new { ObligorID = g.Key, Descriptions = String.Join(", ", g })
    .ToList();

The above is untested and I can't even guarantee that it is correct at this time, but I think it is close to what you need.

(I invite others who identify errors or have improvements to post a comment or directly update this answer.)

Follow up: After writing this up, I did a search on "LINQ to STRING_AGG" and the following was one of the top results: Converting T-SQL string_agg to LINQ C#. Worth a look.

Collectives™ on Stack Overflow

LINQ Multiple Join FOR XML Lambda Query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related