LINQ ICollection of a Model to a string with Name and Surname from within another model

Question

I have a field which is a collection of a model ICollection "Parties", which is in another model "Matter". I want to extract the Name and Surname of each party and separate with a "/" using LINQ. I am putting these into a field called Investors, the second last item.

This is my query:

    var matterQuery = (from matter in context.Matters
  select new
  {
      matter.ID,
      matter.AttorneyRef,
      matter.Status,
      matter.ProductType,
      matter.IsDiscarded,
      matter.Base_CreatedDate,
      matter.Base_CreatedBy,
      matter.BranchID,
      matter.InstitutionID,
      matter.InvestmentTypeID,

      StatusName = matter.MatterStatu != null ? matter.MatterStatu.Name : string.Empty,
      ProductTypeName = matter.ProductType1 != null ? matter.ProductType1.Name : string.Empty,
      AccountNumber = matter.Account != null ? matter.Account.AccountNumber : string.Empty,
      CreatedBy = matter.Paralegal != null ? matter.Paralegal 
      : matter.AspNetUser != null ? matter.AspNetUser.FirstName + " " + matter.AspNetUser.LastName
      : matter.User != null ? matter.User.Firstname + " " + matter.User.Surname
      : string.Empty,
      IntermediaryName = matter.IntermediaryBranch != null ? matter.IntermediaryBranch.Intermediary.Name 
      : matter.Attorney != null ? matter.Attorney.AttorneyName
      : string.Empty,
      CloseRequested = matter.CloseAccounts.Where(x => x.Status == (int)CloseAccountStatusEnum.Authorised).Count() > 0,
      StatementRequested = matter.Account != null ? matter.Account.StatementRequested : false,
      RequestedDate = matter.RequestedDate.HasValue ? matter.RequestedDate.Value : matter.Base_CreatedDate,
      Investors = matter.Parties.Select(x => x.Name.ToString()).Concat(matter.Parties.Select(x => x.Surname.ToString())),
      SoftLock = matter.SoftLock,
  })
   .AsQueryable();

I need this in 1 long string as I am doing a search on the results for a filter and the following line:

(x.Investors != null && x.Investors.ToLower().Contains(search))

requires it be in a string format. I want to display the results as "Name1 Surname1 / Name2 Surname2 / ..."

How can I do this?

EDIT:

I have tried Join and Aggregate but I get the errors:

base {System.SystemException} = {System.NotSupportedException: LINQ to Entities does not recognize the method 'System.String Join(System.String, System.Collections.Generic.IEnumerable`1[System.String])' method, and this method cannot be translated into a store expression. at System.Da...

and...

System.NotSupportedException: LINQ to Entities does not recognize the method 'System.String Aggregate[String](System.Collections.Generic.IEnumerable1[System.String], System.Func3[System.String,System.String,System.String])'

What's the output type of your search? IQueryable? IEnumerable? — Sparrow, Mar 29 '17 at 13:42
The matterQuery is IQueryable. I do a seach on it later using linq's Where so that remains an IQueryable. The Investors field I am trying to get is currently an IEnumerable — 6dev6il6, Mar 29 '17 at 13:47
You can add AsEnumerable() which would make the result into an IEnumerable, thus you can use string.Join(). — Cetin Basoz, Mar 29 '17 at 15:16
BTW you don't need to write like that. You could write like: StatusName = matter.MatterStatu.Name ?? "" ... — Cetin Basoz, Mar 29 '17 at 15:26

Sparrow · Answer 1 · 2017-03-29T15:10:32.347

1

Assuming that your search is working and the result of the search is IEnumerable<string>, you can do this to create the string that you want:

// IEnumerable<string> searchResult = ... this is your search statement
string result = String.Join(" / ", searchResult);

edited Mar 29 '17 at 15:10

answered Mar 29 '17 at 13:56

Sparrow

2,548
1
24
28

The search is done after the initial query, thats why I need 1 long string from the query before I search to be able to use ToLower(). See my edit for when I try use a join – 6dev6il6 Mar 29 '17 at 14:01
1

You can't use the String.Join inside the LINQ query, because your LINQ query will be translated into a SQL query and the engine is not able to create the SQL equivalent of the String.Join. Basically, you cannot use C# statements inside LINQ query. That means you have yo get the query result (as IEnumerable), then convert it to array and use String.Join – Sparrow Mar 29 '17 at 14:08
It won't allow me. I tried Join and Join<> and I get the error : "The call is ambiguous between the following methods or properties: 'string.Join(string, params object[])' and 'string.Join(string, IEnumerable)'" – 6dev6il6 Mar 29 '17 at 14:43
1

I changed the statement. Try it again. – Sparrow Mar 29 '17 at 15:10
Thanks for your help Sparrow, I understand a lot more now ;) I posted my answer below. – 6dev6il6 Mar 30 '17 at 11:07

score 0 · Answer 2 · edited May 23 '17 at 12:09

0

If i did understand your problem right, you have two options:

Bring all results from database and do the join locally
Call a sp to do that

Check this Is there a "for xml path" equivalent in LINQ to SQL?

edited May 23 '17 at 12:09

Community

1
1

answered Mar 29 '17 at 13:42

bruno.almeida

2,746
1
24
32

score 0 · Accepted Answer · answered Mar 30 '17 at 11:06

I solved this by:

In the original query:

select new{
Investors = matter.Parties.Select(x => x.Name + " " + x.Surname), 
}

In the search:

matterQuery.Where(x =>
(x.Investors.Any(y => y.ToLower().Contains(searchString)))
);

When transferring data to a ViewModel:

Investors = String.Join(" / ", matter.InvestorNames),

LINQ ICollection of a Model to a string with Name and Surname from within another model

3 Answers3