No mapping exists from DbType System.String[] to a known SqlCeType.

Question

User1519602350 posted

I tried to add "AND Url LIKE @0" but it throws an error. Please refer the coding below.

    commandText = @"SELECT Pages.PageId, Url, Pages.DateCreated, Title, ContentId, Content, Published FROM Pages INNER JOIN PageContent ON Pages.PageId = PageContent.PageId WHERE Pages.PageId AND Url LIKE @0 IN (SELECT PageId FROM Pages ";
    if(!includeArchived) {
        commandText += "WHERE DateArchived IS NULL ";
    } 
    commandText += "ORDER BY PageId, Url DESC OFFSET @1 ROWS FETCH NEXT @2 ROWS ONLY) ORDER BY Pages.DateCreated DESC";

Answer 1

User1713851783 posted

I'm not sure I have understood your query.

If you want to filter the record that have the PageIds returned by the query

SELECT PageId FROM Pages ORDER BY PageId, Url DESC OFFSET @1 ROWS FETCH NEXT @2 ROWS ONLY

and

Url LIKE @0

you could try to modify your code as follows

    commandText = @"SELECT Pages.PageId, Url, Pages.DateCreated, Title, ContentId, Content, Published FROM Pages
            INNER JOIN PageContent ON Pages.PageId = PageContent.PageId WHERE Pages.PageId IN (SELECT
            PageId FROM Pages ";
    if(!includeArchived) {
        commandText += "WHERE DateArchived IS NULL ";
    } 
    commandText += @"ORDER BY PageId, Url DESC OFFSET @1 ROWS FETCH NEXT @2 ROWS ONLY) AND Url LIKE @0 
            ORDER BY Pages.DateCreated DESC";

 

Answer 2

User-821857111 posted

Somewhere, you seem to have passed an array in as a parameter value. That's what is causing the error. 

Answer 3

User1519602350 posted

Now i am getting the below error,

There was an error parsing the query. [ Token line number = 4,Token line offset = 82,Token in error = @0 ]

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlServerCe.SqlCeException: There was an error parsing the query. [ Token line number = 4,Token line offset = 82,Token in error = @0 ]

Source Error:

Line 33: commandText += "ORDER BY PageId DESC OFFSET @1 ROWS FETCH NEXT @2 ROWS ONLY ) ORDER BY Pages.DateCreated DESC"; Line 34: Line 35: var pages = db.Query(commandText, searchWords, offset, pageSize).GroupBy(p => p.PageId); Line 36: Line 37: var draftActions = new List<string> {"\"Preview\""};