Migrate a Sql query to EF linq

JF Bertrand • February 26, 2019

Migrate a Sql query to EF linq

February 25, 2019

If you are like me, you write queries in SQL and you use Dapper (or some other cool ORM) to connect to your database, run your queries and you are as happy as a clam.

 

Sometimes, however, you get to a point where doing what you've always done, just isn't cool anymore.  Let's say, for example, that you want to work with EF and code first, well, it would go a little bit against the flow to tell your peeps, well, the EF Framework/Code First thing is cool, but I want to use Dapper.  Now you've become un-cool ...

 

So for the few of us that need to understand how to build a join in EF Linq, I give you this tutorial.

 

Writing in Linq

Let's start by writing the query we want.

select t1.*, t2.*, (t1.Value1 - t2.value2) as CalculatedValue
from Table1 t1 join Table2 on t1.table2_id = t2.id
where CalculatedValue > 1000


 

This is a very simple query but caused me great pains to try to write in Linq.

First off you need to make sure you have all the correct using statements.

    using System.Collections.Generic;
    using System.Linq;
    using Microsoft.EntityFrameworkCore;

 

 

 Now that we have this figured out, let's look at the Linq query:

 

var recommendations =
 this.dbContext.Table1
 .Join( this.dbContext.Table2,
 t1 => t1.table2_id,
 t2 => t2.id,
 (t1, t2) => new
 {  Table1 = t1,
    Table2 = t2,
    CalculatedValue = t1.Value1 - t2.value2
 } )
 .Where(x => x.CalculatedValue > 1000)
 .ToList();

 

All of this may be obvious to some of you but it took me a minute for it to click.

Let's try to do this in words:

Table1.Join(tableToJoin, table1OnParameter, table2OnParameter, ObjectThatRepresentsTheSelect).Where(WhereStatement) ..

 

A little more details:

This:

from table1 join table1 on t1.table2_id = t2.id

Translates to

Table1.Join( this.dbContext.Table2, t1 => t1.table2_id,  t2 => t2.id,

 

Are you starting to see it now?

So this:

select t1.*, t2.*, (t1.Value1 - t2.value2) as CalculatedValue

Translates to this

(t1, t2) => new {  Table1 = t1,    Table2 = t2,   CalculatedValue = t1.Value1 - t2.value2 }

 

 

I know you are seeing it.

 

So it took a while an the syntax may be counter intuitive but once you understand at least this part, you can start writing complex queries using Linq.  Just be careful to inspect the query before you deploy just in case it's not optimized.

 

Now you go out there and enjoy your Entity Framework Linq queries ... ;)

Adding notification to your Ionic app using the realtime framework (realtime.co)

By JF Bertrand • September 2, 2017
Adding notification to your Ionic app using the realtime framework (realtime.co) September 1, 2017 | JF Bertrand If you already have an existing Ionic app and would like to add Messaging and Notification to it you can sometimes run into challenges. I looked long and hard, and after a few failures, I found a very easy library that takes most of the complications out of play. Realtime.co offer an almost magical way to deal with both Android and iOS notifications and messaging. When I say almost magical is mostly because iOS makes everything more complicated than it needs to be. In this post, I will help you navigate through some of the hurdles I ran into while implementing realtime on Ionic v1. In a nutshell In a very simpleton way to look at it, all you have to do is register your products with the appropriate authorities (google and Apple), add those ids to your realtime.co profile, add a little bit of code in your app to register the realtime engine and you are done!. How to do it First things first, you have to create a realtime.co account, go here for that. Once you have created you account, realtime has a great tutorial on how to get you started, for the most part you can follow the instructions in this example. I suggest you do the Google the firebase cloud messaging first since it's spot on. I suggest you go do that and come back to me when you have completed the first few steps, go ahead, I'll see you before you start APNS (Apple). Android is done, now what? So, you've created your Firebase Cloud account and you are so excited because you think you are going to be done in just a few hours, then you get to this page. All of a sudden, your heart stops for a second and you hear yourself say "F...., why is apple so complicated?". I can't answer that question but I can offer a little relief since I found a few unnecessary steps in the process of getting APNS setup. I already had a working app, so I didn't need to create an apple id for my application, and since this article is about adding push notification to an existing (and working) application, I will skip this part. Where Apple is a little more a pain in the neck is that it requires you to create a bran new certificate for your push notifications. The documentation implies that you have to first create a 'test' certificate and then create a 'production' certificate, I didn't do that. Since Apple now allows you to test with TestFlight out of the box, you can simply create prod certificates and use those throughout your process. Wait? I haven't touched my app yet ... You are right, what to do, or 'where' to do it. I struggled a little in finding the 'where' to put the realtime specific code to activate push notifications and messaging, but after a few trial and error, I settled with the activation in my index.html page and the registration in my login controller. Put this in the header of your index.html file And you can put something like this after your app in initialized, I put mine in my login controller, after successful login. function setupSubscriptions() { if (window.plugins && window.plugins.OrtcPushPlugin) { var OrtcPushPlugin = window.plugins.OrtcPushPlugin; var personalChannel = "user-" + $scope.playerInfo.player_id; OrtcPushPlugin.subscribe({ 'channel': personalChannel }); userService.getGroupSubscriptions($scope.playerInfo.player_id) .then(function () { OrtcPushPlugin.enableHeadsUpNotifications(); for (var i = 0; i < userService.groupSubscriptions.length; i++) { var groupChannel = "group-" + userService.groupSubscriptions[i].group_id; OrtcPushPlugin.subscribe({ 'channel': groupChannel }); } }); } } I am adding a bunch of subscriptions based on my users group membership, this may or may not be needed for all apps. Okay, that's great, what do I do when I get a push notification? That's a great question, what I did is add this document.addEventListener("push-notification", showAlert, false); function showAlert(notification) { $ionicPopup.alert({ title: notification.payload.title, template: notification.payload.message, okText: 'OK' }); }; In the same controller (login) as the previous steps. This will produce a nice little message box for my users to discard after they have read the message. The end Over all it took me only a couple of days to setup everything and get push notifications out to my app. If you get stuck and need more help, they have a great push notification tool to help you send messages out to your device and the customer support was second to none. I ran into only a few issues but most of them were around getting the APNS setup and where to activate the plugin. I hope this will help some developer out there.