Convert SQL to Dot Notion Linq

Question

How to convert the following sql query into dot notion linq query?

 SELECT     f1.device_id, f1.device_model, f2.main_serial_number
 FROM       Device AS f1 
 INNER JOIN MainSerialNumber AS f2 ON f1.device_id = f2.device_id
 WHERE      (f2.main_serial_number IN
                (SELECT    f2_1.main_serial_number
                 FROM      Device AS f1_1 
                 INNER JOIN MainSerialNumber AS f2_1 
                         ON f1_1.device_id = f2_1.device_id
                 GROUP BY f2_1.main_serial_number
                 HAVING    (COUNT(f2_1.main_serial_number) > 1)))

[see this]( http://stackoverflow.com/questions/630045/linq-dot-notation-vs-query-expression) — ericyoung, Sep 17 '12 at 07:57
I can use query notion to do that. the problem is "IN" part. — ericyoung, Sep 17 '12 at 07:57
In the future, you might want to pick more expressive variable names than `f1`, `f2`, `f1_1`, and `f2_2`. It's a bit difficult to read. — Thom Smith, Sep 17 '12 at 13:29

Thom Smith · Answer 1 · 2012-09-17T15:34:26.027

1

As a query comprehension:

var serialNumbers =
    from sn in MainSerialNumber
    join dev in Device
        on sn.DeviceId equals dev.DeviceId
        into devices
    where devices.Count() > 1
    select sn.MainSerialNumber;

var result =
    from dev in Device
    join sn in MainSerialNumber
        on dev.DeviceId equals sn.DeviceId
    where serialNumbers.Contains(sn.MainSerialNumber)
    select new {
        dev.DeviceId,
        dev.DeviceModel,
        sn.MainSerialNumber
    }

Using method syntax ("dot notation"):

var serialNumbers = MainSerialNumber
    .GroupJoin(Device,
        sn  => sn.DeviceId,
        dev => dev.DeviceId,
        (sn, devs) => new
        {
            Serial = sn.MainSerialNumber,
            Count  = devs.Count()
        }
    )
    .Where(x => x.Count > 1)
    .Select(x => x.Serial);

var result = Device
    .Join(MainSerialNumber,
        dev => dev.DeviceId,
        sn  => sn.DeviceId,
        (dev, sn) => new
        {
            dev.DeviceId,
            dev.DeviceModel,
            sn.MainSerialNumber
        }
    )
    .Where(x => serialNumbers.Contains(x.MainSerialNumber));

For a query like this, the query syntax looks a lot cleaner to me.

edited Sep 17 '12 at 15:34

answered Sep 17 '12 at 13:26

Thom Smith

13,916
6
45
91

Not sure why, but it gets all the data, instead of what I want. – ericyoung Sep 17 '12 at 15:28
Which query? And what, in words, do you expect the query to fetch? This should fetch all matching ID/Model/Serial values such that the serial is associated with more than one Device. – Thom Smith Sep 17 '12 at 15:32
There was a typo in the query comprehension that would have kept it from compiling, so I'm assuming that you used the method syntax. I'll check it over. – Thom Smith Sep 17 '12 at 15:35
I want to get info (device_ID, Device_Model, Main_Serial_Number) that Main_Serial_Number appears more than once in the MainSerialNumber table. I know there could be a typo in your script and tried different variations, but still get incorrect result. – ericyoung Sep 17 '12 at 20:34

Convert SQL to Dot Notion Linq

1 Answers1