searching using linq

Question

i am new to linq and having a hard time to implement searching using two table , i have a sql server stored procedure that is working fine , but i want to do it with linq and its both are looking very different can not able to implement count of user and Contains Properly , can anyone help me on this here is my sql to which i am trying to implement in linq

 @pTeamName Varchar (25) = 'ALL',
 @pUserFirstName Varchar (25) = 'ALL'
    select 
        t.TeamId,TeamName,[Description],
        COUNT(u.UserId)as UserCount from Team t
  left outer join  [User] u on u.TeamId=t.TeamId
  WHERE
    (t.TeamName Like '%'+@pTeamName+'%' OR @pTeamName Like 'ALL')
  AND (u.FirstName = @pUserFirstName OR @pUserFirstName Like 'ALL')
  AND t.Deleted = 0
  group by 
  TeamName,
  [Description],
  t.TeamId

and here is my linq what i have so far

from t in Teams
join u in Users on t.TeamId equals u.TeamId
where t.TeamName.Contains("Tester")
select new {t.TeamName,t.Description,u.UserId}

This may help out :http://stackoverflow.com/questions/27730575/linq-to-sql-left-outer-join-with-group-by-and-having-clause — Ehsan Sajjad, Feb 24 '15 at 14:41

score 0 · Answer 1 · answered Feb 24 '15 at 14:58

0

You can try like this,

    var result = from t in Teams
    join u in Users on t.TeamId equals u.TeamId into j1
    where (t.TeamName.Contains("Tester") || t.TeamName.Contains("ALL") ) && (u.FirstName.Contains("username") || u.FirstName.Contains("ALL")) && t.Deleted = 0
    from j2 in j1.DefaultIfEmpty()
    Group j2 by new
     {
       t.TeamName,
       t.Description,
       t.TeamID,
      } into gcs
   select new {TeamName = gcs.key.TeamName,Description = gcs.Key.Description,Count = gcs.Count(x=>x.UserId)}

Not tested may want to check for the syntax error

answered Feb 24 '15 at 14:58

Mahesh

8,694
2
32
53

Thanks for your Reply , yeah i am getting some syntax error – Miranda Feb 24 '15 at 15:41
i am not familiar with, can you please help – Miranda Feb 24 '15 at 15:56
Can you put some data and you expected output data in question so I can give you the exact expression. – Mahesh Feb 25 '15 at 05:25
Suppose User input for TeamName is "Te" then all teamname that contains te should be listed, or if user input "Tester" for Teamname and "Mark" as userName then output should be the particular record only which name is mark and team is Tester . – Miranda Feb 25 '15 at 11:29

searching using linq

1 Answers1