none
How I can do this subquery in Linq to sql RRS feed

  • Question

  • I have this query in sql:

    SELECT        MM.ID, MM.[Content],
                                 (SELECT        C.FullName
                                   FROM            ClientMessages AS CM INNER JOIN
                                                             Clients AS C ON CM.IDClient = C.ID
                                   WHERE        (CM.IDMessage = MM.ID) AND (CM.Owner = 1)) AS Creator
    FROM            MessageModels AS MM INNER JOIN
                             ClientMessages AS MCM ON MCM.IDMessage = MM.ID INNER JOIN
                             Clients AS MC ON MCM.IDClient = MC.ID
    WHERE        (MC.ID = 3)
    ORDER BY Creator

    How I can do this subquery in Linq to sql?

    so I have yet one linq query but that's not succesfull because I want to show the creator column:

     int IDUser = Convert.ToInt32(Session["user"]);
                var result = (from clientMessage in _db.ClientMessage
                              join message in _db.Message on clientMessage.IDMessage equals message.ID
                              join client in _db.Client on clientMessage.IDClient equals client.ID
                              where clientMessage.IDClient == IDUser
                              orderby clientMessage.Owner
                              select new MessageInformation { Message = message, Client = client, ClientMessage = clientMessage }).ToList();

    • Moved by Bob Beauchemin Saturday, April 21, 2012 12:05 AM Moved to the LINQ to SQL forum (From:.NET Framework inside SQL Server)
    Friday, April 20, 2012 9:22 PM

Answers

  • Hi,

    You can run your tsql directly by "ExcuteQuery" method here: http://msdn.microsoft.com/en-us/library/bb399403.aspx


    I am fish.

    Monday, April 23, 2012 12:38 PM

All replies

  • I have this query in sql:

    SELECT        MM.ID, MM.[Content],
                                 (SELECT        C.FullName
                                   FROM            ClientMessages AS CM INNER JOIN
                                                             Clients AS C ON CM.IDClient = C.ID
                                   WHERE        (CM.IDMessage = MM.ID) AND (CM.Owner = 1)) AS Creator
    FROM            MessageModels AS MM INNER JOIN
                             ClientMessages AS MCM ON MCM.IDMessage = MM.ID INNER JOIN
                             Clients AS MC ON MCM.IDClient = MC.ID
    WHERE        (MC.ID = 3)
    ORDER BY Creator

    How can I do this subquery in Linq to sql?

    so I have yet one linq query but that's not succesfull because I want to show the creator column:

     int IDUser = Convert.ToInt32(Session["user"]);
                var result = (from clientMessage in _db.ClientMessage
                              join message in _db.Message on clientMessage.IDMessage equals message.ID
                              join client in _db.Client on clientMessage.IDClient equals client.ID
                              where clientMessage.IDClient == IDUser
                              orderby clientMessage.Owner
                              select new MessageInformation { Message = message, Client = client, ClientMessage = clientMessage }).ToList();
    Friday, April 20, 2012 9:26 PM
  • I have this query in sql:

    SELECT        MM.ID, MM.[Content],
                                 (SELECT        C.FullName
                                   FROM            ClientMessages AS CM INNER JOIN
                                                             Clients AS C ON CM.IDClient = C.ID
                                   WHERE        (CM.IDMessage = MM.ID) AND (CM.Owner = 1)) AS Creator
    FROM            MessageModels AS MM INNER JOIN
                             ClientMessages AS MCM ON MCM.IDMessage = MM.ID INNER JOIN
                             Clients AS MC ON MCM.IDClient = MC.ID
    WHERE        (MC.ID = 3)
    ORDER BY Creator

    How can I do this subquery in Linq to sql?

    so I have yet one linq query but that's not succesfull because I want to show the creator column:

     int IDUser = Convert.ToInt32(Session["user"]);
                var result = (from clientMessage in _db.ClientMessage
                              join message in _db.Message on clientMessage.IDMessage equals message.ID
                              join client in _db.Client on clientMessage.IDClient equals client.ID
                              where clientMessage.IDClient == IDUser
                              orderby clientMessage.Owner
                              select new MessageInformation { Message = message, Client = client, ClientMessage = clientMessage }).ToList();
    Friday, April 20, 2012 9:47 PM
  • Hi,

    You can run your tsql directly by "ExcuteQuery" method here: http://msdn.microsoft.com/en-us/library/bb399403.aspx


    I am fish.

    Monday, April 23, 2012 12:38 PM
  • I think your LINQ query must look something like this:

    var result = (from MM in _db.MessageModels join
    MCM in _db.ClientMessages on MCM.IDMessage equals MM.ID join
    MC in _db.Clients on MCM.IDClient equals MC.ID
    where MC.ID = 3
    select new MessageInformation 
    {
    	Message = message, 
    	Client = client, 
    	ClientMessage = clientMessage,
    	Creator = (from CM in _db.ClientMessages join
    	           C in _db.Clients on CM.IDClient equals C.ID
    			   where (CM.IDMessage = MM.ID) && (CM.Owner = 1)
    			   select C.FullName).SingleOrDefault()
    	
    })


    Please mark as reply if helped.
    Also visit my blog http://msguy.net/

    Tuesday, April 24, 2012 8:24 AM
  • Hi!! your idea was excellent... but when I code that asp throws an exception and say I have to use the FirstOrDefault() method instead of SingleOrDefault()... so when I change the method the creator stay null..... what can I do? 
    Wednesday, April 25, 2012 4:58 PM