locked
How to do a select distinct with EF? RRS feed

  • Question

  • Hi guys

    I've got a table with a few columns but just need to select distinct(columnName) on one of them.  I'm executing as follows:

    Dim context = New ArticleContext
    Dim results = context.Articles.SqlQuery("select distinct publicationYearShort from articles order by publicationYearShort").ToList()

    This results in the following error:

    The data reader is incompatible with the specified 'ArticlesModel.Article'. A member of the type, 'articleID', does not have a corresponding column in the data reader with the same name.

    If I do a "select * from articles" no problem, but then I can't get distinct records.

    Any suggestions?

    Tks


    Steven

    Friday, February 8, 2013 4:42 PM

Answers

  • Hi Steven;

    The raw SQL query that you are sending to the server from context.Articles.SqlQuery is expecting records of type Articles but what is being returned is a single column of values of publicationYearShort and therefore the reason for the exception, it is expecting all columns from the table.

    Is there a reason why you need to use a raw SQL query? If you do not need to use a rqw SQL query then you can use the following to do the same.

    Dim results = (From pys in context.Articles
                   Order By pys.publicationYearShort
                   Select pys.publicationYearShort).Distinct().ToList()


      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".



    Friday, February 8, 2013 6:40 PM

All replies

  • Hi,

    As you are using Articles.SqlQuery (DbSet.SqlQuery) the query is expected to return article entities.

    Instead you could use context.Database.SqlQuery to return a list of whatever best fit your needs (same type than publicationYearShort)...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Friday, February 8, 2013 6:24 PM
  • Hi Steven;

    The raw SQL query that you are sending to the server from context.Articles.SqlQuery is expecting records of type Articles but what is being returned is a single column of values of publicationYearShort and therefore the reason for the exception, it is expecting all columns from the table.

    Is there a reason why you need to use a raw SQL query? If you do not need to use a rqw SQL query then you can use the following to do the same.

    Dim results = (From pys in context.Articles
                   Order By pys.publicationYearShort
                   Select pys.publicationYearShort).Distinct().ToList()


      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".



    Friday, February 8, 2013 6:40 PM