CODEWITHSUNDEEP
c#.netsql-serverlinqlinq-to-sql
λ Jonas Gorauskas
λ Jonas Gorauskas

Reputation: 6198

LINQ to SQL Association Mapping

I am working on a simple command line app to teach myself some LINQ to SQL in C#. I have a SQL Server 2008 instance and I am trying to look in MSDB for the Jobs currently setup on the SQL Server. I want to output the Job Name a dot and the Step name and the actual SQL statement to do that is the following: 

use msdb
go

select j.name + '.' + s.step_name
from sysjobs j
join sysjobsteps s on j.job_id = s.job_id
order by j.name
go

I am at a loss for how to establish the relationship between SysJobs and SysJobSteps in the C# code and I keep getting the following error message:

System.InvalidOperationException: Invalid association mapping for member 
'ServerCompare.Lib.Commands.SysJob.SysJobSteps'. 
'ServerCompare.Lib.Commands.SysJob' is not an entity.

Please advise what is the best way to do this?

Upvotes: 7

Views: 3762

Answers (2)

Craig Stuntz
Craig Stuntz

Reputation: 126547

Both tables must have a defined primary key. Use Column(Name="whatever_id", IsPrimaryKey=true)

Upvotes: 18

AD.Net
AD.Net

Reputation: 13399

You need to create a dbml file first, adding those two tables and then use something like this:

 using (var context = new DataClasses1DataContext())
            {
                var result = from s in context.sysjobs
                             orderby s.name
                             join sjs in context.sysjobsteps on s.job_id equals sjs.job_id
                             select new {Name = s.name + "." + sjs.step_name};

            }

To create a dbml with system tables you need to do create a connection first, then right-click the server -> change view -> object type, you should now see all system tables. Then add the two tables.

Upvotes: 3

Related Questions