C# Linq Entity Conversion Error on Nonexistent Value?

Question

While trying to query some data using the Linq Entity Framework I'm receiving the following exception:

{"Conversion failed when converting the varchar value '3208,7' to data type int."}

The thing that is confusing is that this value does not even exist in the view I am querying from. It does exist in the table the view is based on, however. The query I'm running is the following:

return context.vb_audit_department
                .Where(x => x.department_id == department_id && x.version_id == version_id)
                .GroupBy(x => new { x.action_date, x.change_type, x.user_ntid, x.label })
                .Select(x => new
                {
                    action_date = x.Key.action_date,
                    change_type = x.Key.change_type,
                    user_ntid = x.Key.user_ntid,
                    label = x.Key.label,
                    count = x.Count(),
                    items = x
                })
                .OrderByDescending(x => x.action_date)
                .Skip(startRowIndex)
                .Take(maximumRows)
                .ToList();  

Can somebody explain why LINQ queries the underlying table instead of the actual view, and if there is any way around this behavior?

Answer 1

before we can answer you last question we need to more know about the context the linq query is executing (and if EF or LinqToSql how the mapping is defined)

Answer 2

Linq to SQL/EF do not query the underlying table instead of a view. It has no knowledge of this information.

That error is coming from SQL Server itself. It looks like the view may be defined with a CAST that is failing for certain rows. It is entirely possibly to define a view this way, such that it will only fail when you try to SELECT from it, and you will receive that exact error message.

To demonstrate:

CREATE TABLE Test
(
    Value varchar(50)
)

INSERT Test (Value) VALUES ('1')

CREATE VIEW TestView AS
    SELECT CAST(Value AS int) AS Value
    FROM Test

SELECT * FROM TestView    -- Succeeds

INSERT Test (Value) VALUES ('Some String')

SELECT * FROM TestView    -- Will fail with your error message

So, again, there is a good chance that the problem is with the view itself if you're sure that this bad value is not supposed to appear in the view at all. Either that or your EF mapping is wrong (i.e. you have the wrong type defined for one of the columns). This can also cause that error because EF will insert a CAST.