CODEWITHSUNDEEP
c#entity-frameworklinq
thomsan
thomsan

Reputation: 483

Error getting when trying assign linq data into DTO class

I need to assign Linq data into my DTO class, but this Linq query getting mt this error.

LINQ to Entities does not recognize the method 'System.String ToString(System.String, System.IFormatProvider)' method, and this method cannot be translated into a store expression.

I removed int convertion to avoid the error, then I got this error

the type of one of the expressions in join clause is incorrect. type inference failed in the call to join

My code as follows,

public class TextDto
{
    public string AddedData { get; set; }
    public string AddedTime { get; set; }
    public string Sender { get; set; }
    public string Name { get; set; }
    public string Messsage { get; set; }
}

linq code,

var allText = from nm in context.NotificationMessages
join u in context.Users on new {
    Sender = (int) nm.Sender
}
equals new {
    Sender = u.UserId
}
select new {
    nm.Id,
    nm.AddedTime,
    nm.Lobby.Branch.BranchName,
    u.FirstName,
    u.LastName,
    nm.Message
};

var allTextList = allText.Select(data = >new TextOnDemandDto {

    AddedTime = data.AddedTime.ToString("hh:mm tt", CultureInfo.InvariantCulture),
    Messsage = data.Message,
    Name = data.FirstName + " " + data.LastName

}).ToList();

This sql, which I converted into linq

SELECT nm.id,
       nm.addedtime,
       b.branchname,
       u.firstname,
       u.lastname,
       nm.message
FROM   notificationmessage nm
       INNER JOIN lobby l
               ON l.lobbyid = nm.fklobbyid
       INNER JOIN branch b
               ON b.branchid = l.fkbranchid
       INNER JOIN [user] u
               ON nm.sender = u.userid

Upvotes: 1

Views: 142

Answers (1)

Serge
Serge

Reputation: 43860

Try to change your query to this:

var allText = (from nm in context.NotificationMessages
join u in context.Users 
on nm.Sender equals  u.UserId into ug
 from u in ug.DefaultIfEmpty()
join l in context.Lobbys 
 on    nm.fklobbyid eguals  l.lobbyid into lg
 from l in lg.DefaultIfEmpty()
 join b in  context.Branches 
  on l.fkbranchid  equals b.branchid into bg
 from b in bg.DefaultIfEmpty()
select new {
    nm.Id,
    nm.AddedTime,
    b.BranchName,
    u.FirstName,
    u.LastName,
    nm.Message
}).ToList();

Update:

To get records with FKNotificationMessageTypeId = 3 insert where clause between "from b.." and "select":


... from b ...
where nm.FKNotificationMessageTypeId == 3
select new ....

Upvotes: 2

Related Questions