DataContext invalid object

Question

I am using VS2010 C# Express and MS SQL Server 2012.

Using C# I'm trying to query a test database I have created.

My C# code is as follows:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    using System.Text;

    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                DataContext db = new DataContext("C:\\Program Files\\Microsoft SQL         Server\\MSSQL10.SQLEXPRESS\\MSSQL\\DATA\\test.mdf");
                Table<Portfolios> PORTFOLIO = db.GetTable<Portfolios>();
                var q =
                    from c in PORTFOLIO
                    where c.PORTF_CODE == "PTF1"
                    select c;
                Console.WriteLine(q);
                foreach (var portf in q)
                {
                    Console.WriteLine("id = {0}, City = {1}", portf.PORTF_CODE,         portf.PORTF_NAME);
                    Console.ReadLine();
                }
            }
        }

        [Table(Name = "PORTFOLIO")]
        public class Portfolios
        {
            [Column(IsPrimaryKey = true)]
            public int PORTF_ID;
            [Column]
            public string PORTF_CODE;
            [Column]
            public string PORTF_NAME;
            [Column]
            public int BENCH_ID;
            [Column]
            public int CCY_ID;
        }
    }

The PORTFOLIO table that I am trying to query was created using the below code:

CREATE TABLE PORTFOLIO
( PORTF_ID      INT             IDENTITY(10000, 1)          PRIMARY KEY CLUSTERED
, PORTF_CODE    VARCHAR(25)     NOT NULL
, PORTF_NAME    VARCHAR(200)    NOT NULL
, BENCH_ID      INT             
, CCY_ID        INT)

INSERT INTO PORTFOLIO(PORTF_CODE, PORTF_NAME)
VALUES('PTF1', 'PTF1 - Portfolio 1');

Once I have created the above, I detach the database and run the c# code. The error I get is when I step over the code line:

foreach (var portf in q)

The error says: Invalid object name 'PORTFOLIO'. I assumed this meant that it couldn't find the PORTFOLIO object in my database, but I have checked several times that it is there and that there is data in it. I have even recreated the database several times to be sure.

Can anyone spot why it can't find the PORTFOLIO Object please? Thanks

Answer 1

It's not really clear why you are experiencing this kind of problem but nothing is wrong with your code and with few modifications it was also worked on my computer. What you should be aware to is the fact that you're working against the DB file which the MSSQL server in your computer is working against too (as your MDF file path looks like now).

It would be much better to work against detached database in order to avoid of accessibility problems.

Accordingly, I would suggest you to take the following steps as I did to solve it:

1. Detach your "test" database from the MSSQL server in order to work against it independently: At the MSSMS -> Right-Click on your DB -> Tasks -> Detach -> Select your the "test" DB to detach.

2. Open this folder: "C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA" and copy test.mdf and test_log.ldf files to "C:\Databases" (Create this folder first).

3. I've change your code a bit so copy and try to run it:

using System;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;

namespace ConsoleApplication1
{
    static class Program
    {
        static void Main()
        {
            var db = new DataContext("C:\\Databases\\test.mdf");

            var portfolio = db.GetTable<Portfolios>();

            var result =
                from c in portfolio
                where c.PORTF_CODE == "PTF1"
                select c;

            foreach (var item in result)
            {
                Console.WriteLine("id = {0}, City = {1}", item.PORTF_CODE, item.PORTF_NAME);
            }

            Console.ReadLine();
        }
    }

    [Table(Name = "PORTFOLIO")]
    public class Portfolios
    {
        [Column(IsPrimaryKey = true)]
        public int? PORTF_ID;

        [Column]
        public string PORTF_CODE;

        [Column]
        public string PORTF_NAME;

        [Column]
        public int? BENCH_ID;

        [Column]
        public int? CCY_ID;
    }
}