ToList() throws an exception

Question

I try to retrieve a list from the data base. However when I call the ToList() method it throws and exception.Knowing that the database is not empty.

 var listeRef = from r in db.refAnomalies
                       select r;
        rapportAnomalie rp = new rapportAnomalie();
        List<refAnomalie> listeRefference = listeRef.ToList();

The exception : {"Invalid column name 'rapportAnomalie_code_rapport'."}

this is my database tables:

CREATE TABLE [dbo].[refAnomalie] (
[code_anomalie]    INT            IDENTITY (1, 1) NOT NULL,
[libelle_anomalie] NVARCHAR (100) NOT NULL,
[score_anomalie]   INT            NOT NULL,
[classe_anomalie]  NVARCHAR (100) NOT NULL,
CONSTRAINT [PK_dbo.refAnomalie] PRIMARY KEY CLUSTERED ([code_anomalie] ASC)
);


 CREATE TABLE [dbo].[rapportAnomalie] (
[code_rapport] INT           IDENTITY (1, 1) NOT NULL,
[date_rapport] DATETIME      NOT NULL,
[etat]         NVARCHAR (50) NOT NULL,
[code_agence]  INT           NOT NULL,
CONSTRAINT [PK_dbo.rapportAnomalie] PRIMARY KEY CLUSTERED ([code_rapport]    ASC),
CONSTRAINT [FK_dbo.rapportAnomalie_dbo.agence_code_agence] FOREIGN KEY    ([code_agence]) REFERENCES [dbo].[agence] ([code_agence]) ON DELETE CASCADE
  );


 GO
 CREATE NONCLUSTERED INDEX [IX_code_agence]
  ON [dbo].[rapportAnomalie]([code_agence] ASC);

rapportAnomalie Class :

  [Table("rapportAnomalie")]
   public partial class rapportAnomalie
    {
    [Key]
    public int code_rapport { get; set; }

    public DateTime date_rapport { get; set; }

    [Required]
    [StringLength(50)]
    public string etat { get; set; }

    public int code_agence { get; set; }
    [ForeignKey("code_agence")]
    public agence agence { get; set; }

    public List<refAnomalie> listeRefAnomalie { get; set; }
    public List<ligneRapportAnomalie> listeLigneRapportAnomalie { get; set;       }
}
}

Anyone knows how to fix it?

I didn't define it. even rapportAnomalie and the Context file doesn't have it — Omar Chaabouni, Jul 09 '15 at 00:21
Are you using Linq2SQL or EF? Your linq query queries a table `refAnomalies`, but you posted the definition and class for a different table (`rapportAnomalie`). Please post the `refAnomalie` table as well. — Sphinxxx, Jul 09 '15 at 00:34
It seems you're using code first.. Can you show us the Mapping class for the same? It looks interesting issue. — vendettamit, Jul 09 '15 at 01:27

score 1 · Accepted Answer · answered Jul 09 '15 at 00:46

I replaced

var listeRef = from r in db.refAnomalies
                   select r;

with

String sql = @"select * from refAnomalie";
        List<refAnomalie> listeRefference = new List<refAnomalie>();
        var con =  new SqlConnection("data source=(LocalDb)\\MSSQLLocalDB;initial catalog=Banque;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework");
        using (var command= new SqlCommand(sql,con)){

            con.Open();
            using (var reader = command.ExecuteReader())
            {

                while (reader.Read())
                    listeRefference.Add(new refAnomalie
                    {
                        code_anomalie = reader.GetInt32(0),
                        libelle_anomalie = reader.GetString(1),
                        score_anomalie = reader.GetInt32(2),
                        classe_anomalie = reader.GetString(3)
                    });
                    }

        }

and it worked fine

score 0 · Answer 2 · answered Jul 09 '15 at 00:51

The error message is quite clear: EF evidently generates a query simliar to...

select ..., rapportAnomalie_code_rapport, ...
from refAnomalie

...and the error tells you that there is no rapportAnomalie_code_rapport column in refAnomalie.

You need to take a look at the class that EF generates for refAnomalie. There is probably a reference to that mysterious column somewhere in there.

score 0 · Answer 3 · edited May 23 '17 at 11:51

@Omar, Changing the complete approach is not the solution of the problem. The way I see this problem is you're having naming convention issue with Navigation properties. Which sometime confuses the EF and results in generating a "Guessed" column name which doesn't exist.

I would recommend to thoroughly go through this Discussion about navigation property and name generations. Also check how to overcome this using InverseProperty attribute.

Since We don't have complete details of your EF code first entities it's really hard to point out the problem area. But hope above suggestions will help you re-visit your code and identify the problem.

ToList() throws an exception

3 Answers3