C# Convert SQL query to LINQ

Question

I have what I think is a complex SQL query, and i need to convert it to LINQ query. But, I'm only a beginner in LINQ and don't know how to get this working. I've tried searching the internet and still unable to get it to work.

Here is my SQL query:

SELECT
    a.empid,
    CAST(IF(COUNT(*)  > 10,FLOOR(COUNT(*) / 10),1) AS CHAR(100)) lim,
    CAST(GROUP_CONCAT(internalid) AS CHAR) internalIDS
FROM tblLogs a
INNER JOIN
    (
        SELECT 
            DISTINCT empid 
        FROM tblLogs
        WHERE IsDeleted = 0 AND DateAdded = 2013-04-18
    ) b ON  a.empid = b.empid
WHERE IsDeleted = 0 AND Remarks NOT LIKE '%proxy date used%' AND DateAdded = 2013-04-18 AND RecType = 8
GROUP BY empid;

Hi. Here is my updated linq query, but it returns an error Linq to Entities does not recognize the method string.join. What's wrong with this? Thanks. :)

var rows = from rec in context.tblWMSLogs join rec1
                        in context.tblWMSLogs.Where(t => t.DateAdded == refDate2 && t.IsDeleted == 0)
                        on rec.EmpID equals rec1.EmpID
                    where rec.DateAdded == refDate2 && rec.IsDeleted == 0 && !rec.Remarks.Contains("proxy date used") && rec.RecType == recType
                    group rec by rec.EmpID into g
                    select new WMSRandomViewModel
                    {
                        EmpID = g.Key,
                        Lim = (g.Count() > 10 ? Math.Ceiling(g.Count() / 10d) : 1),
                        InternalIDs = string.Join(",", g.OrderBy(s => s.InternalID).Select(s => s.InternalID))
                    };//string.Join(",", g.OrderBy(s => s.InternalID).Select(s => s.InternalID))
            //return rows.ToList();
            return rows.ToList();

Does your attempt compile? If so, does it give you what you want? What exactly is your question? — ean5533
– ean5533, Commented Apr 22, 2013 at 3:25

Ethan Li · Accepted Answer · 2013-04-22 06:06:16Z

1

is this like what you want?

var rows =
        from rec in context.tblLogs.AsEnumerable()
        join rec1 
            in context.Where(t => t.DateAdded == refDate && t.IsDeleted == 0)
            on rec.EmpID equals rec1.EmpID
        where rec.DateAdded == refDate && rec.IsDeleted == 0 && !rec.Remarks.Contains("proxy date used") && rec.RecType == recType
        group rec by rec.EmpID into g
        select new
        {
            g.Key,
            lim = (g.Count() > 10 ? Math.Floor(g.Count() / 10d) : 1).ToString(),
            InternalIDS = string.Join("", g.OrderBy(s => s.InternalId).Select(s => s.InternalId))
        };

now, my question,

why is there a inner join in your sql? could the where clause in the inner join be moved to the where clause of the main query

edited Apr 22, 2013 at 6:06

answered Apr 22, 2013 at 4:56

Ethan Li

1,0011 gold badge13 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

finnTheHumin Over a year ago

hi. there is an inner join because the data can come from different tables.

Ethan Li Over a year ago

ah~ ok, so the tblLogs table could be a different table?

finnTheHumin Over a year ago

Hi. Thanks. It's now showing any errors now but i still am not able to get the query results. I'm using mysql as my database. Maybe it has some issues on the DateTime part of the where clause.

Ethan Li Over a year ago

you can try to remove rec.DateAdded == refDate, and check if you get results

Ethan Li Over a year ago

are you using something which translate linq in to MySql script? if this is the case you can't use string.Join if the library you use does not support the method. so what you are actually doing with the linq? what library you are using? have you research on how the library implements GROUP_CONCAT?

Collectives™ on Stack Overflow

C# Convert SQL query to LINQ

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related