LINQ to SQL Association Mapping

Question

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?

I have posted sample code in C# of what I am trying to accomplish here: gist.github.com/947347 — λ Jonas Gorauskas
– λ Jonas Gorauskas, Commented Apr 28, 2011 at 21:13

Craig Stuntz · Accepted Answer · 2012-04-11 19:31:28Z

18

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

answered Apr 11, 2012 at 19:31

Craig Stuntz

127k12 gold badges257 silver badges275 bronze badges

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

1 Comment

bopapa_1979 Over a year ago

I'm pretty sure this is the answer that will solve the problem for most people.

AD.Net · Accepted Answer · 2011-04-28 21:09:52Z

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.

Collectives™ on Stack Overflow

LINQ to SQL Association Mapping

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related