In this post we will explore the most practical ways of performing left outer joins in LINQ to Entities. The same principles applying to LINQ to Entities for EF 4 also apply to LINQ2SQL. This was not the case before EF 4 mainly due to the absence of the DefaultIfEmpty() method in the Entity Framework. In an case, in this post we focus on EF4.
For our test we will use the following demo database/conceptual model:
As you can see, Master records may have 1 or more Detail records and Detail records may have one or more DetailOfDetail records. Left outer joins are translated as follows:
“Bring me all records/entities from the Master table along with their corresponding Detail records. The Master records should always exist in the result set even if there are no related records/entities in the Detail table”.
The first approach is using the navigation property Details in the query as follows:
from p in ctx.Masters.Include("Details") select p
Query1() method in the project
This approach is the simplest possible. It only applies when you have the “Details” Navigation Property on your model (enabling the property just to be able to perform left outer join queries is not an option) and it also suffers from the fact that the data are not flattened (which you may need since your data are feeding a report for example). You get a list of Master objects each one with a list of 0 or more Detail objects.
But you have a way of flattening your results. Using the following LINQ will flatten your results but unfortunately will perform an inner join:
var Result2 = from p in ctx.Masters
from q in p.Details
select new {Id = p.Id, m1 = p.MasterField1,m2 = p.MasterField2,
d1=q.DetailField1,d2=q.DetailField2};
Query2() method in the project
To do a left outer join you just need to do the following:
var Result2 = from p in ctx.Masters
from q in p.Details.DefaultIfEmpty()
select new {Id = p.Id, m1 = p.MasterField1,m2 = p.MasterField2,
d1=q.DetailField1,d2=q.DetailField2};
Query3() method in the project
That is, the only difference is the addition of the .DefaultIfEmpty() method (this one was missing in previous versions of EF). Now you have a flattened result set for your needs.
The second approach does not require the navigation property:
If you do not have the “Details” navigation property then you can still get a left outer join. Using the following will give you an inner join:
var Result3 = from p in ctx.Masters join q in ctx.Details on p.Id equals q.MasterID
select new {Id = p.Id,m1 = p.MasterField1,m2 = p.MasterField2,
d1 = q.DetailField1,d2 = q.DetailField2};
Query4() method in the project
Changing this to the followinq:
var Result3 = from p in ctx.Masters
join q in ctx.Details on p.Id equals q.MasterID into Details
select new {Id = p.Id,d=Details};
Will give you the same result as if you had the navigation property. All you have to do for the left outer join is to flatten it as before:
var Result3 = from p in ctx.Masters
join q in ctx.Details on p.Id equals q.MasterID into Details
from m in Details.DefaultIfEmpty()
select new {Id = p.Id,m1 = p.MasterField1,m2 = p.MasterField2,
d1 = m.DetailField1,d2 = m.DetailField2};
Query5() method in the project
Now let’s extend it a little further and require to get results from the third table as well. We will distinguish again between the two previous approaches (obviously we would like to avoid a nasty foreach loop at the end where the data are collected with SELECT statements creating a wonderful SELECT N+1 issue).
Using the Navigation Property Approach
To include the data from the third table in your result you just follow the same principle by adding the third table:
var Result2 = from p in ctx.Masters
from q in p.Details.DefaultIfEmpty()
from l in q.Details.DefaultIfEmpty()
select new {Id = p.Id, m1 = p.MasterField1,m2 = p.MasterField2,
d1=q.DetailField1,d2=q.DetailField2,dd1=l.DetailOfDetailField1,dd2=l.DeailOfDetailField2};
Query6() method in the project
Without the Navigation Property
The same applies here by following the same approach as before:
var Result3 = from p in ctx.Masters
join m in ctx.Details on p.Id equals m.MasterID into Details
from q in Details.DefaultIfEmpty() join o in ctx.DetailOfDetails
on q.Id equals o.DetailID into DetailsOfDetail
from l in DetailsOfDetail.DefaultIfEmpty()
select new
{
Id = p.Id,
m1 = p.MasterField1,
m2 = p.MasterField2,
d1 = q.DetailField1,
d2 = q.DetailField2,
dd1 = l.DetailOfDetailField1,
dd2 = l.DeailOfDetailField2
};
Query7() method in the project
The project can be downloaded here
(Note that since in the project we are not using the database the DefaultIsEmpty method gets a parameter defining the default object.)
.aspx)
In this post, we will see how easy it is to create an XNA world for Windows Phone 7 that obeys the laws of physics. To our quest, our weapon will be the Farseerphysics engine. You will be amazed when you realize how interesting a simple circle on the phone’s display becomes when you add some physics to its world! So let our quest begin…
Download the zip file from the project’s site. Unzip its contents and locate the folder containing the sources of the Farseer engine (at the time of this writing the folder is named “Farseer Physics Engine 3.2 XNA”).
Open Visual Studio and create a new project of “Windows Phone Game'” type (important note: if you are a newbie in XNA game development you are strongly encouraged to follow thisseries of blog posts and also download the developer tools from here).
The game we will be making will be as follows: The user sees a sprite on the screen and it gives momentum to the sprite (yellow ball) by applying a gesture to the phones display. The sprite should be bouncing at randomly placed obstacles on the display and it should hit the randomly placed silver balls (ouaou that’s interesting!)
In an XNA Game for WP7 your canvas is 800x480 pixels size. Therefore we have created a background image in Photoshop of this size. The sprites that will be used in our game are the following:
And now it is time to get started. When you have created your “Windows Phone Game” project, Visual Studio has created an extra project where you will add your sprites. We add all the aforementioned images (sprites) there following the well known “Add Item” procedure:
Now go to your solution’s folder and copy the Farseer project you have located before along with its containing folder to your solution folder. Right-click on your solution and select “Add/Existing Project”. Select the .csproj file for WP7 of the Farseer physics engine located in the folder you ‘ve just copied. Finally add a reference at your XNA game project to the engine (if you feel lost download the project at the end of this post and you will see the final state of the folders/projects). If you did everything correctly the solution will compile successfully. And this is all the plumbing you have to do. Now, let us do some physicz!
First we create the definitions of our sprites along with some lists of the positions of the obstacles and balls:
private Texture2D _background;
private Texture2D _myBall;
private Texture2D _floor;
private Texture2D _otherBall;
private Texture2D _obstacle;
private List<Vector2> _otherBallPositions;
private List<Vector2> _obstaclePositions;
private Random _r;
The definition of the random number generator will be used in the creation of the scene. In the LoadContent() method we create out Textures and select their positions on the display (note the random creation of a number of obstacles and balls and their positions):
protected override void LoadContent()
{
spriteBatch = new SpriteBatch(GraphicsDevice);
_background = Content.Load<Texture2D>("Background");
_floor = Content.Load<Texture2D>("Floor");
_myBall = Content.Load<Texture2D>("MyBall");
_obstacle = Content.Load<Texture2D>("Obstacle");
_otherBall = Content.Load<Texture2D>("OtherBall");
_obstaclePositions = new List<Vector2>();
for (int i = 0; i < _r.Next(1, 6); i++)
_obstaclePositions.Add(new Vector2(_r.Next(100, 700), _r.Next(100, 400)));
_otherBallPositions = new List<Vector2>();
for (int i = 0; i < _r.Next(1, 6); i++)
_otherBallPositions.Add(new Vector2(_r.Next(100, 700), _r.Next(100, 400)));
}
Initially we will draw everything on the display in the Draw() method as follows:
protected override void Draw(GameTime gameTime)
{
GraphicsDevice.Clear(Color.CornflowerBlue);
spriteBatch.Begin();
spriteBatch.Draw(_background, new Vector2(0, 0), Color.White);
spriteBatch.Draw(_floor, new Vector2(0, 459), Color.White);
spriteBatch.Draw(_myBall, new Vector2(40, 432), Color.White);
for (int i = 0; i < _obstaclePositions.Count; i++)
spriteBatch.Draw(_obstacle, _obstaclePositions
, Color.White);
for (int i = 0; i < _otherBallPositions.Count; i++)
spriteBatch.Draw(_otherBall, _otherBallPositions, Color.White);
spriteBatch.End();
base.Draw(gameTime);
}
This will generate a nice scene but everything will be static. Here is where we apply out physics engine to bring everything to life. Define a World object and create it in the Initialize method:
private World _world;
protected override void Initialize()
{
_r = new Random();
_world = new World(new Vector2(0,9.81F));
base.Initialize();
}
The parameter is the gravity which we set it as a vector perpendicular to our x-axis (as is in real life). Now for every sprite in your world you need to define its Body (mass, physical properties) and its Shape (its geometry). In the Farseer physics world you have to define the two and then connect them (to represent a single object) with a Fixture, actually saying that a body with mass X will be represented as a rectangle (for example) with Y,Z dimensions. Since this may be too much code to write, you may use a factory to do the job for you. For example for the floor (a rectangular body at the bottom of our game scene) we define:
In the LoadContent() method we create the Fixture for the floor which automatically defines a body of mass with density 1 and a rectangular shape of size the same as the floor’s texture:
private Fixture _floorFixture;
protected override void LoadContent()
{
(…)
_floor = Content.Load<Texture2D>("Floor");
(…)
_floorFixture=FixtureFactory.CreateRectangle(_world,_floor.Width,_floor.Height,1);
_floorFixture.Body.BodyType = BodyType.Static;
_floorFixture.Body.Position=new Vector2(0, 459);
(…)
}
Note that there is no connection with the texture, at least in code. The only thing we use the texture for is when we draw it on the position of the body. In the update method, we tell our world to process the position of the bodies (the Step method gets a parameter defining how much time has passed since the last step for the world) and then in the draw we use the result to draw the floor. The time that has passed is the actual-real time:
protected override void Update(GameTime gameTime)
{
(…)
_world.Step(gameTime.ElapsedGameTime.TotalSeconds);
base.Update(gameTime);
}
protected override void Draw(GameTime gameTime)
{
(…)
spriteBatch.Draw(_floor, _floorFixture.Body.Position, Color.White);
(…)
base.Draw(gameTime);
}
Here is the first tricky part. If you use the aforementioned placement, the position of the floor texture will not correspond to the position of the floor body. This is because FarSeer considers the local shape’s origin point (0,0) to be at the center of the rectangle while XNA considers the same point (0,0) to be at the top left cornet of the rectangle. This is illustrated in the following figure:
Due to this, the “FarSeer world” and the “Visible world” have different opinions on where the body is located since the position of a body/texture defines the position of its local origin:
To compensate for this you need to do two things. First, place the floor to location (400,469), which will place the body in the correct position in the FarSeer world (left) and then change the local origin when drawing the texture from (0,0) to (400,10) (right) which will also position correctly the texture in the “Visible world”:
This in code is translated as follows:
(…)
// The following is in the LoadContent() method!
_floorFixture=FixtureFactory.CreateRectangle(_world,_floor.Width,_floor.Height,100);
_floorFixture.Body.BodyType = BodyType.Static;
_floorFixture.Body.Position=new Vector2(400, 469); //THIS IS DIFFERENT NOW
(…)
// The following is in the Draw() method!
spriteBatch.Draw(_floor, _floorFixture.Body.Position, null, Color.White, _floorFixture.Body.Rotation,
new Vector2(_floor.Width / 2,_floor.Height/2), 1, SpriteEffects.None, 1);
When you compile and run the program now nothing seems to have changed but believe me the physics world is there. To prove this just change the definition of Body.BodyType from BodyType.Static to BodyType.Dynamic and the floor will just fall from its position obeying the Newtonian rule of gravity! Let’s now follow the same approach for the other sprites in our world (the principle is exactly the same so below is the complete listing):
protected override void LoadContent()
{
spriteBatch = new SpriteBatch(GraphicsDevice);
_background = Content.Load<Texture2D>("Background");
_floor = Content.Load<Texture2D>("Floor");
_myBall = Content.Load<Texture2D>("MyBall");
_obstacle = Content.Load<Texture2D>("Obstacle");
_otherBall = Content.Load<Texture2D>("OtherBall");
_floorFixture=FixtureFactory.CreateRectangle(_world,_floor.Width,_floor.Height,1);
_floorFixture.Body.BodyType = BodyType.Static;
_floorFixture.Restitution = 0.7f;
_floorFixture.Body.Position=new Vector2(400, 469);
_otherBallFixtures = new List<Fixture>();
for (int i = 0; i < _r.Next(1, 16); i++)
{
Fixture Temp = FixtureFactory.CreateCircle(_world, _otherBall.Height / 2, 1);
Temp.Body.Position = new Vector2(_r.Next(100, 700), _r.Next(100, 400));
Temp.Body.IsStatic = false;
Temp.Restitution = 0.7f;
_otherBallFixtures.Add(Temp);
}
_obstacleFixtures = new List<Fixture>();
for (int i = 0; i < _r.Next(1, 6); i++)
{
Fixture Temp = FixtureFactory.CreateRectangle(_world,_obstacle.Width,_obstacle.Height,100);
Temp.Body.BodyType = BodyType.Static;
Temp.Body.Position=new Vector2(_r.Next(100, 700), _r.Next(100, 400));
Temp.Restitution = 0.7f;
_obstacleFixtures.Add(Temp);
}
}
And the Draw() is as follows:
protected override void Draw(GameTime gameTime)
{
GraphicsDevice.Clear(Color.CornflowerBlue);
spriteBatch.Begin();
spriteBatch.Draw(_background, Vector2.Zero, Color.White);
spriteBatch.Draw(_floor, _floorFixture.Body.Position, null, Color.White,
_floorFixture.Body.Rotation, new Vector2(_floor.Width / 2,_floor.Height/2),
1, SpriteEffects.None, 1);
spriteBatch.Draw(_myBall, new Vector2(40, 432), Color.White);
for (int i = 0; i < _obstacleFixtures.Count; i++)
spriteBatch.Draw(_obstacle, _obstacleFixtures.Body.Position, null, Color.White,
_obstacleFixtures.Body.Rotation,
new Vector2(_obstacle.Width / 2, _obstacle.Height / 2), 1,
SpriteEffects.None, 1);
for (int i = 0; i < _otherBallFixtures.Count; i++)
spriteBatch.Draw(_otherBall, _otherBallFixtures.Body.Position, null, Color.White,
_otherBallFixtures.Body.Rotation,
new Vector2(_otherBall.Width / 2, _otherBall.Height / 2), 1,
SpriteEffects.None, 1);
spriteBatch.End();
base.Draw(gameTime);
}
Note that we have also defined the Restitution coefficient for our bodies which defines how much of the initial velocity is lost when the ball will bounce on the obstacle (1 for example will have the ball bouncing endlessly like being made from a very elastic rubber, while a 0 will make the ball behave like it is made from iron).
Now when you run the program all the balls will fall to the floor and will also bounce. But the first thing you will notice is that they are too slow. You can go experimenting by raising the gravity value or increasing the time in the Step() method but those approaches are all trial and error and you will not get to far by this. The actual problem is the fact that you are playing with very large numbers in the FarSeer engine. While gravity and mass are defined in Kg and m/sec2 without any issues, you are defining shapes like the floor that are 800 meters long and 20 meters tall since the pixel to meter ratio is 1:1. This causes the engine to overflow. To get more realistic results you need to define the pixels/meter ratio to something different. In our case, let’s say that 1 pixel is 50 meters. This means that our view will be 16m wide and 9,6m tall (800x480 pixels) which is fine. Define a new private field as follows:
private float _pixelsPerMeter = 50;
Now when you define a shape you need you divide by this. For example:
_floorFixture = FixtureFactory.CreateRectangle(_world, _floor.Width / _pixelsPerMeter,
_floor.Height / _pixelsPerMeter, 1f);
_floorFixture.Body.BodyType = BodyType.Static;
_floorFixture.Restitution = 0.7f;
_floorFixture.Body.Position = new Vector2(400 / _pixelsPerMeter, 469 / _pixelsPerMeter);
That is you are translating everything from pixels to meters for the FarSeer world. When you draw you need to do the opposite as follows:
spriteBatch.Draw(_floor, _floorFixture.Body.Position * _pixelsPerMeter, null, Color.White,
_floorFixture.Body.Rotation * _pixelsPerMeter,
new Vector2(_floor.Width / 2, _floor.Height / 2) , 1, SpriteEffects.None, 1);
Now when you compile and run everything will make more sense. It takes approx. 1 sec for the ball to fall from the top to the bottom (4km) at 9.81 g. Well let’s see:
Well as you can see you have a perfectly realistic world that obeys the laws of physics without experimenting. If you change your ratio to 25 your world is bigger, your balls are higher and it will take them twice the time to reach the bottom. The only thing left to do is apply some force to the yellow ball by the use of gestures.
First we define the yellow ball as a body too, like we did for the other sprites and then we add in the Update() method the following:
while (TouchPanel.IsGestureAvailable)
{
GestureSample gesture = TouchPanel.ReadGesture();
if (gesture.GestureType == GestureType.Flick)
_myBallFixture.Body.ApplyForce(Vector2.Divide(gesture.Delta, 2.0f));
}
This will constantly check for gestures and apply them as a force to the ball. The last thing to be done is to enable this type of gestures at the initialization method as follows:
TouchPanel.EnabledGestures = GestureType.Flick;
And this concludes our introduction to the physics engine. The project can be downloaded
.