4 Replies - 3206 Views - Last Post: 03 November 2010 - 05:24 AM Rate Topic: -----

#1 Sergio Tapia  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1251
  • View blog
  • Posts: 4,168
  • Joined: 27-January 10

Exception raised when using a Linq query with Entity Framework

Posted 01 November 2010 - 07:20 PM

Using C# 4.0 with Windows Forms. Also using SQLite with Entity Framework.

Here's what I'm trying to do. User selects a State, then a ComboBox loads with Cities in that State, then when user selects a City a Listbox loads all projects from that City.

When running the method that's supposed to load the projects to the ComboBox, I receive this error:

Quote

Exception has been thrown by the target of an invocation. Inner Exception is: "{"Specified cast is not valid."}"valid."}"


This is the method that causes the exception:

private void LoadProjectsToListBox(long idCity)
    {
        ProjectRepository projectRepo = new ProjectRepository();    
    
        //This line causes the error.
        var projects = projectRepo.FindAllProjects().Where(c => c.IDCity == 1).ToList();
    }


In the Linq query, you'll see that I'm comparing against a hard coded number. 1, is a valid ID of a City that has Projects so it should work. The exception is only raised when I give the Linq query a City ID that has Projects. So it seems the error is raised when the query returns results.

Here is the SQL statements I used to generate the Project and City tables, there is no datatype inconsistancy as far as I can tell:

    create table City
    (
    	ID integer primary key autoincrement,
    	Name string,
    	IDState integer references State(ID)
    );
    
    create table Project
    (
    	ID integer primary key autoincrement,
    	Name string,
    	StartDate text,
    	IDManager integer references Manager(ID),
    	IDCity integer references City(ID),
    	IDDepartment integer references Department(ID),
    	ContactNumber string,
    	Description string
    );


What strikes me as odd is that using the exact same pattern and access code to show a list of Departments (just another table in my database) everything works as expected. For example here's how I load Departments right now just for testing:

    private void LoadProjectsToListBox(long idCity)
    {
        ProjectRepository projectRepo = new ProjectRepository();
        DepartmentRepository departmentRepo = new DepartmentRepository();
    
        //Doesn't work - raises exception.
        var projects = projectRepo.FindAllProjects().Where(c => c.IDCity == 1).ToList();
    
        //Works exactly as expected.
        var deps = departmentRepo.FindAllDepartments().Where(c => c.IDParentDepartment == 7).ToList();
    
        lstProjects.Items.Clear();
        foreach (var item in deps)
        {
            lstProjects.Items.Add(item.Name);
        }
    }


The FindAllDepartments(), FindAllCities(), FindAllProjects() methods are essentially the same.

DocumentsDBEntities db = new DocumentsDBEntities();

    public IQueryable<Project> FindAllProjects()
    {
        return db.Projects;
    }

    public IQueryable<City> FindAllCities()
    {
        return db.Cities;
    }

    public IQueryable<Department> FindAllDepartments()
    {
        return db.Departments;
    }



As mentioned before, Department fetching works; Project fetching causes the exception.

Is This A Good Question/Topic? 0
  • +

Replies To: Exception raised when using a Linq query with Entity Framework

#2 mavarazo  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 35
  • View blog
  • Posts: 179
  • Joined: 25-October 10

Re: Exception raised when using a Linq query with Entity Framework

Posted 02 November 2010 - 10:57 AM

It's hard to find the Error, but i would search bottom up. I don't think it's an error of your coding.
Search in the Database for something that isn't right.
Was This Post Helpful? 0
  • +
  • -

#3 Sergio Tapia  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1251
  • View blog
  • Posts: 4,168
  • Joined: 27-January 10

Re: Exception raised when using a Linq query with Entity Framework

Posted 03 November 2010 - 05:19 AM

OK, in case anyone is interested in this problem, hell it might help someone, here's what I did to make it work:

The Project table had a column with the SQLite datatype 'text'. That caused the queries to fail! I have no idea why though. If someone could shed some light that would be awesome! This has to be the hardest bug I've ever had to track down.

WORKS:

create table Project
(
	ID integer primary key autoincrement,	
	IDManager integer references Manager(ID),
	IDCity integer references City(ID),
	IDDepartment integer references Department(ID),
	Name string,
	ContactNumber string,
	Description string,
        StartDate string
);



Doesn't work:

create table Project
(
	ID integer primary key autoincrement,
	Name string,
	StartDate text, //DANGER WILL ROBINSON. CAUSES SHIT TO BREAK!
	IDManager integer references Manager(ID),
	IDCity integer references City(ID),
	IDDepartment integer references Department(ID),
	ContactNumber string,
	Description string
);


Was This Post Helpful? 0
  • +
  • -

#4 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: Exception raised when using a Linq query with Entity Framework

Posted 03 November 2010 - 05:22 AM

Why are you setting a "Date" field to be a string anyway? Why not use a Date/Time field type?
Was This Post Helpful? 0
  • +
  • -

#5 Sergio Tapia  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1251
  • View blog
  • Posts: 4,168
  • Joined: 27-January 10

Re: Exception raised when using a Linq query with Entity Framework

Posted 03 November 2010 - 05:24 AM

Quote

1.2 Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1