How to get unique records using LINQ when columns are all varchar

Question

I am trying to figure out a way using linq to get the most recent records from a database. All of the fields in the table are varchar2.

For example I have a table:

CODE   VERSION   FIELD1   FIELD2 ...
AAA    0.0.1     data1    data2
BBB    0.0.2     data22   xxxxx
AAA    0.0.2     xxcxcx   asdasdas
AAA    0.0.3     data11   data5555
BBB    0.0.2     dasds    aasdasd

I would like only to return.

AAA    0.0.3     data11   data5555
BBB    0.0.2     dasds    aasdasd

I could bring all the data back ordering by CODE/VERSION then take the top 1 for each unique code. However that seems like I'm adding overhead. Is there a simpler way to accomplish this?

EDIT: I am using Oracle 11g. I have no control on the current DB the fields are what they are. I am trying LINQ-Entities and the Code and version are the keys to the table.

Linq-To-What, f.e. Linq-To-Entities? What is the key, `CODE` or multiple columns? **Edit** since it seems to be oracle i think you're using `Linq-To-Entities` — Tim Schmelter, Jul 20 '16 at 12:28
Also, since the `VERSION` column is crucial for the ordering you should refactor your model. Create `MajorVersion`, `MinorVersion`, `Revision` int-columns and the ordering is simple. — Tim Schmelter, Jul 20 '16 at 12:33
Ordering by Version is going to be a problem if you can have values like 0.0.10 as that would come before 0.0.2 lexically. The best solution would be to separate the parts of the version into separate columns if possible. Otherwise you're looking at either pulling all the data back and doing it in code or a very inefficient SQL query. — juharr, Jul 20 '16 at 12:33
@TimSchmelter he could parse his `VERSION` varchar into the type `Version` and have all those properties. I doubt SQL server supports the version type, so this would have to happen on the client. — Marco, Jul 20 '16 at 12:34
@Marco: but then he would do the ordering and filtering in memory instead of in the database which is clearly not what he wants if the table becomes large — Tim Schmelter, Jul 20 '16 at 12:35
Alternatievly he could use HierachyId as pointed out here http://stackoverflow.com/questions/12933610/datatype-for-system-version-in-sql-server — Marco, Jul 20 '16 at 12:38

ken2k · Accepted Answer · 2016-07-20T12:49:05.473

3

If your requirement is something like "for each unique code", this generally means you need to use a GroupBy.

Here's a working example for LINQ-To-Object:

var data = new List<MyData>
{
    new MyData { Code = "AAA", Version = "0.0.1", Field1 = "data1", Field2 = "data20" },
    new MyData { Code = "BBB", Version = "0.0.2", Field1 = "data2", Field2 = "data21" },
    new MyData { Code = "AAA", Version = "0.0.2", Field1 = "data3", Field2 = "data22" },
    new MyData { Code = "AAA", Version = "0.0.3", Field1 = "data4", Field2 = "data23" },
    new MyData { Code = "BBB", Version = "0.0.2", Field1 = "data5", Field2 = "data24" },
};

var res = data
    .GroupBy(z => z.Code)   // Group by code
    .Select(z => z
        .OrderByDescending(z2 => z2.Version)
        .FirstOrDefault())  // For each group, select the most recent one. This assumes you can order versions using their textual representation
    .ToList();

EDIT

Looks like you can't change what you store in your Oracle database. Sorting on the VERSION column probably won't work (one can assume you won't stop versioning after 9.9.9).

Either you load everything in memory, then parse the VERSION field, then sort. Either you write a stored procedure (or a view) that efficiently does this on the DB server.

edited Jul 20 '16 at 12:49

answered Jul 20 '16 at 12:35

ken2k

48,145
10
116
176

1

The old problem: `0.0.10` comes before `0.0.2` – Tim Schmelter Jul 20 '16 at 12:38
@TimSchmelter Yes, this obviously only works if you can sort versions using their textual representation. – ken2k Jul 20 '16 at 12:41
It only works if neither the Major- nor the Minorversion or Revision will ever exceed 9. So it's a timebomb :) – Tim Schmelter Jul 20 '16 at 12:42
Yes. Just saw the edit on the OP, if they can't change what they store (basically a timestamp in addition to the varchar2), this is gonna be a problem. – ken2k Jul 20 '16 at 12:44
He could load all into memory (f.e. with `AsEnumerable()`) and then he could split the string by `.` into three tokens to feed the [`Version` constructor](https://msdn.microsoft.com/en-us/library/00dkw4kk(v=vs.110).aspx) which can be used for the `OrderByDescending`. But then he has to load the table always into memory which is not an option if its large – Tim Schmelter Jul 20 '16 at 12:47
This query also returns `BBB 0.0.2 data22 xxxxx` instead of `BBB 0.0.2 dasds aasdasd` as OP wants for `BBB` group. – Salah Akbari Jul 20 '16 at 12:48
OK- For those using Oracle 11 you will get the error: "Oracle 11.2.0.3.0 does not support APPLY". I just added the .ToArray after the groupby. – John Doe Jul 20 '16 at 12:52
1

@JDS I'm not familiar with the EF provider for Oracle (so I'm not sure what the error message means and can't easily provide you a fix), but calling ToArray() after the groupBy will materialize all your entities on memory. This is gonna be OK if your table doesn't contain a lot of data, this is gonna be a problem if your table contains millions of entries. – ken2k Jul 20 '16 at 12:55
1

@JDS Instead of `ToArray` you should use `AsEnumerable` to convert your Linq over to Linq to Objects. This will avoid an unneeded intermediate array. – juharr Jul 20 '16 at 12:56

How to get unique records using LINQ when columns are all varchar

1 Answers1