LINQ to SQL query

  • Thread starter Thread starter Paolo
  • Start date Start date
P

Paolo

I'm struggling with LINQ to SQL queries. I have two tables with the following
relationship:

Activities (many) ---- (one) Types, where Types.Type_Id is a description for
Activities.Activity_Type. So I am trying to use Types as a look up table to
provide the description for a single character column in Activities.

I have defined entity classes thus:

[Table(Name="Activities")]
public partial class Activity
{
#region Fields
[Column(IsPrimaryKey = true, DbType="SmallDateTime NOT NULL")]
public DateTime Activity_Date { get; set; }

[Column(DbType = "Char(1) NOT NULL", CanBeNull=false)]
public char Activity_Type { get; set; }

[Column(DbType="TinyInt NOT NULL")]
public byte Activity_Distance {get; set; }

[Column(DbType = "Char(10) NOT NULL", CanBeNull=false)]
public string Activity_Duration {get; set; }

[Column(DbType = "Char(35) NOT NULL", CanBeNull=false)]
public string Activity_Route {get; set; }
#endregion

private EntitySet<Type> _Types;

[Association(Name = "FK_Activities_Types", Storage = "_Types",
OtherKey = "Type_Id", DeleteRule = "NO ACTION")]

public EntitySet<Type> Types
{
get { return this._Types; }
set { this._Types.Assign(value); }
}

}

[Table(Name="Types")]
public partial class Type
{
#region Fields
[Column(IsPrimaryKey = true, DbType = "Char(1) NOT NULL", CanBeNull
= false)]
public char Type_Id { get; set; }

[Column(DbType = "Char(8) NOT NULL", CanBeNull = false)]
public char Type_Desc { get; set; }
#endregion
}

I want to get a listing of all Activities with each activity's Activity_Type
replaced by its Type_Desc from the Types table.

I'd appreciate some help in definig the appropriate query. I've got this far:

var act_types =
from a in db.Activities // db is a typed data context
from t in a.Types
where a.Activity_Type = ????? // not sure how to define
this bit
 
You should do a join between the Activity and the Type tables

var activities = dataContext.GetTable<Activity>();
var types = dataContext.GetTable<Type>();

var act_types =
from a in activities
join t in Types
on a.Activity_Type equals t.Type_Id
select .... select columns here... ;


... hope this helps,
Bennie Haelen
 
Bennie: thank you. I had managed to progress since I posted this thread and,
although slightly different from your suggestion, had got the query to work
as required.

bhaelen said:
You should do a join between the Activity and the Type tables

var activities = dataContext.GetTable<Activity>();
var types = dataContext.GetTable<Type>();

var act_types =
from a in activities
join t in Types
on a.Activity_Type equals t.Type_Id
select .... select columns here... ;


.. hope this helps,
Bennie Haelen
Paolo said:
I'm struggling with LINQ to SQL queries. I have two tables with the following
relationship:

Activities (many) ---- (one) Types, where Types.Type_Id is a description for
Activities.Activity_Type. So I am trying to use Types as a look up table to
provide the description for a single character column in Activities.

I have defined entity classes thus:

[Table(Name="Activities")]
public partial class Activity
{
#region Fields
[Column(IsPrimaryKey = true, DbType="SmallDateTime NOT NULL")]
public DateTime Activity_Date { get; set; }

[Column(DbType = "Char(1) NOT NULL", CanBeNull=false)]
public char Activity_Type { get; set; }

[Column(DbType="TinyInt NOT NULL")]
public byte Activity_Distance {get; set; }

[Column(DbType = "Char(10) NOT NULL", CanBeNull=false)]
public string Activity_Duration {get; set; }

[Column(DbType = "Char(35) NOT NULL", CanBeNull=false)]
public string Activity_Route {get; set; }
#endregion

private EntitySet<Type> _Types;

[Association(Name = "FK_Activities_Types", Storage = "_Types",
OtherKey = "Type_Id", DeleteRule = "NO ACTION")]

public EntitySet<Type> Types
{
get { return this._Types; }
set { this._Types.Assign(value); }
}

}

[Table(Name="Types")]
public partial class Type
{
#region Fields
[Column(IsPrimaryKey = true, DbType = "Char(1) NOT NULL", CanBeNull
= false)]
public char Type_Id { get; set; }

[Column(DbType = "Char(8) NOT NULL", CanBeNull = false)]
public char Type_Desc { get; set; }
#endregion
}

I want to get a listing of all Activities with each activity's Activity_Type
replaced by its Type_Desc from the Types table.

I'd appreciate some help in definig the appropriate query. I've got this far:

var act_types =
from a in db.Activities // db is a typed data context
from t in a.Types
where a.Activity_Type = ????? // not sure how to define
this bit
Click to expand...
Click to expand...
 
Back
Top