Entity Framework Exception in Foreign Key 2

Question

Hi I have two tables like this:

public class TableA
{
    public int IdTableA { get; set; }

    public string OtherInfo { get; set; }

    public int? IdTableB { get; set; }

    public TableB TableB { get; set; }
}

public class TableB
{
    public TableB()
    {
        TableAs = new HashSet<TableA>();
    }

    public int IdTableB { get; set; }

    public string Description { get; set; }

    public ICollection<TableA> TableAs { get; set; }
}

The foreign key mapping in table TableA to TableB is something like this:

this.HasOptional(t => t.TableB)
    .WithMany(t => t.TableAs)
    .HasForeignKey(d => d.IdTableB);

Now I'm trying to select some data from TableA:

context
    .TableA
    .Select(s => new TableA_DTO
    {
        IdTableA = s.IdTableA,
        OtherInfo = s.OtherInfo,
        IdTableB = s.IdTableB,
        TableB = new TableB_DTO
        {
            IdTableB = s.TableB.IdTableB,
            Description = s.TableB.Description
        }
    }).ToList();

The problem that when I have a row in TableA with IdTableB = null. I get this exception:

The cast to value type 'Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

How I can select this info with entity framework?

Brian Mains · Accepted Answer · 2014-06-30 17:27:31Z

1

You need a null check:

context
    .TableA
    .Select(s => new TableA_DTO
    {
        IdTableA = s.IdTableA,
        OtherInfo = s.OtherInfo,
        IdTableB = s.IdTableB,
        TableB = new TableB_DTO
        {
            IdTableB = (s.TableB != null) ? s.TableB.IdTableB : null,
            Description = (s.TableB != null) ? s.TableB.Description : null
        }
    }).ToList();

Or possibly:

TableB = s.TableB != null 
        ? new TableB_DTO
        {
            IdTableB = (s.TableB != null) ? s.TableB.IdTableB : null,
            Description = (s.TableB != null) ? s.TableB.Description : null
        }
        : null

EDIT: Based on the comments below, that error is because TableA_DTO is not known by the context or SQL. What you need to do is first get the resultset efficiently:

var data = context.TableA.Include("TableB").ToList(); 'ToList executes

Then, select this and convert to the desired type:

//This is a LINQ to Objects operation now, which can do any kind of data transformation
var results = data.Select(s => new TableA_DTO { ... });

That will work, but you have to be sure you aren't running into a select N+1 scenario by Entity Framework retrieving related data in the following iterations.

edited Jun 30, 2014 at 17:27

answered Jun 30, 2014 at 15:56

Brian Mains

50.8k35 gold badges155 silver badges260 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jonny Piazzi Over a year ago

In the first case, TableB_DTO.IdTableB is not null (int). In second throw a exception: Unable to create a constant value of type 'TableA_DTO'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

Brian Mains Over a year ago

It's trying to convert TableA_DTO to SQL, and can't. Edited above.

Jonny Piazzi Over a year ago

Thanks. It isn't a cleanest solution, but appears to be the only one.

Collectives™ on Stack Overflow

Entity Framework Exception in Foreign Key 2

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related