0

I have an Entity that has varchar and text columns (Text/CLOB database type).

The following query doesn't work, because MyEntity has Clob columns (Oracle). Similar behavior is expected in SqlServer with its Text columns.

var query = from e in ctx.MyEntity select e.Distinct()

How can I use Linq to retrive distinct rows from this table/entity?

Note: I can't use SqlServer's "varchar(max)"; it must be 'text' type; because in other DBMS like Oracle there's no varchar(max) type.

My issue is due to SqlServer and Oracle compatibility with Linq / Entity Framework.

Note2: I need objects of MyEntity type in the output select, and not generic types and no other kind of types.

I appreciate any suggestions. Thanks.

  • In Sql Server you can use `DISTINCT` with a `SUBSTRING` of a `Text` field. I don't know if this also applies to CLOBs. – Gert Arnold Jul 24 '15 at 22:40
  • Check this out - http://stackoverflow.com/a/10720079/1559611, here you create an anonymous type and use Distinct to get the answer, then check this http://stackoverflow.com/a/10720540/1559611, since you have to override the GetHashCode and Equals method in the resulting type to ensure that Distinct genuinely functions as expected. Currently it will do object reference comparison, which will not help to achieve the desired result. I can provide a code example if its unclear – Mrinal Kamboj Jul 25 '15 at 02:49
  • @GertArnold, Yes, it's also applies to CLOB. Therefore, substring limits a CLOB in only 4000 characters. This is because I prefer building a join. –  Jul 26 '15 at 13:46
  • @MrinalKamboj, that answer is apparently using a generic type. But I will try this way. Thanks –  Jul 26 '15 at 13:48
  • @Raffael Bechara Rameh being a generic solution you can replace for other types easily – Mrinal Kamboj Jul 26 '15 at 14:35
  • @MrinalKamboj Great solution! One question, please: Can I make a cast from a generic type to a POCO defined type, if all the properties are the same? –  Jul 26 '15 at 23:01
  • @Raffael Bechara Rameh Check my answer, that should mostly help in resolving the issue – Mrinal Kamboj Jul 27 '15 at 09:35

0 Answers0