LINQPad 4 for testing BPM queries?


(Caleb Grundmeier) #21

Did you ever get it to work in LinqPad 5? I’m struggling getting it to work too. I’m on 10.2.200.6.


(Haso Keric) #22

You should download Simons Boilerplate:

Epicor Linq BoilerPlate.linq (4.1 KB)

Then once you open it in LINQPad under Actions I believe its F4 or F5 it will bring up a “References Dialog” make sure your referencing the right files.

Make sure your web.config is accessible which you reference in the .linq file and make sure your web.config is not pointing to the DB Server “localhost” if your hosting SQL and AppServer on same Server then you have to change it in the web.config to the actual server name like MY-WINDOWS-SERVER and it should work.

No need to any connections or anything. It will use the ErpContext.

Benefit of using this instead of the LINQ Connection is that you can use Business Objects!


(Haso Keric) #23

What Error make sure your web.config does not point to localhost it wont work because LINQ will try to connect to DB Server localhost aka your own computer and fail.

793b206d7e0b9a86e4f4afd29d5f160e4b3e6518_1_690x205

Change from (local) to SERVERNAME
Benefit of using the boilerplates provided vs direct connection is the actual use of Business Objects.


(Caleb Grundmeier) #24

Maybe I’m missing something but I keep getting this error:
“_MetadataException: Schema specified is not valid. Errors: _
(0,0) : error 0152: No Entity Framework provider found for the ADO.NET provider with invariant name ‘EpiProvider’. Make sure the provider is registered in the ‘entityFramework’ section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

I also had to add the EntityFramework dll as reference for it to compile. This is the first time I’ve tried to use it with 10.2. I had it working with 10.1. Also tried it in LinqPad 4 with no luck.


(Haso Keric) #25

Ah I see. I haven’t tried it with 10.2 yet; I’ll try it later today and I’ll let you know If I get it work.


(Caleb Grundmeier) #26

I finally found the missing pieces to make 10.2 to work.

Add references EntityFramework.dll and EntityFramework.SqlServer.dll
image

Add Namespace “Epicor.Data.Provider”
image

Call EpiProviderRegistrator.Register() after the InitialiseConfiguration(webConfigFile).

image


(Haso Keric) #27

Niceeeeeee!


(Haso Keric) #28

Since we are talking about LINQ - make sure you get https://oz-code.com/ - its magical.


(Caleb Grundmeier) #29

@hasokeric Do you happen to know what piece I would be missing to get this expression to work in LinqPad?

var q2 = Db.UD13.Select(s => 
new { Company = s.Company, OrderNum = Convert.ToInt32(s.Key1), 
OrderLine = Convert.ToInt32(s.Key2), 
OrderRelNum = Convert.ToInt32(s.Key3) }).ToList();

It will compile in the BPM but I get a “LINQ to Entities does not recognize the method ‘Int32 ToInt32(System.String)’ method, and this method cannot be translated into a store expression.” exception. I’m usually pretty good at finding a solution but I’m pretty stumped on this one. It’s probably right in front of my face.

It obviously doesn’t like the Convert.ToInt32. I’m sure I’m just missing a reference or two.


(Chris Conn) #30

Related to this perhaps? Did Microsoft Break Something?


(Caleb Grundmeier) #31

Apparently I was wrong and the same query was not working in the BPM. But I did find that adding
Db.UD13.AsEnumerable() did the trick.


(Chris Conn) #32

That is interesting, in BPM context, I though that was already an enumerable object


(Caleb Grundmeier) #33

That’s what I thought too. I think I’m still missing something but for now I’ll have to use it.


(Chris Conn) #34

@erikj @Bart_Elia Is this a clue to the other issue Jose noted?


(Bart Elia) #35

Not sure - Jose owes us the environment info.

One thing in the thread people corrected but may have missed.

In …?10.2.200? … ( i need to validate)
… We moved to Entity Framework 6. That is a new dll MS ships out of band from the rest of the .NET framework. The correction is as stated above to pull in EntityFramework.dll and EntityFramework.SqlServer.dll. That is a new reference file needed in a lot of places. That should have been in the release notes but probably not in the red flaming fonts as needed.


(Caleb Grundmeier) #36

I’m developing on 10.2.200.6.


(Bart Elia) #37

Then you are using EF 6. Need to add those EF references these days. MSFT dev is all about adding a ton of references - the nuget way of the world now.


(Chris Conn) #38

Can we get those added by default in a BPM - asking for a friend :stuck_out_tongue:


(Bart Elia) #39

Ticket :stuck_out_tongue:


(Caleb Grundmeier) #40

Understood. I guess what I’m getting at, is it possible to use the Convert.To methods when selecting to an anonymous type without having to use the .AsEnumerable Extension prior to the Select? For example:

var q2 = Db.UD13.Select(s => 
new { Company = s.Company, OrderNum = Convert.ToInt32(s.Key1), 
OrderLine = Convert.ToInt32(s.Key2), 
OrderRelNum = Convert.ToInt32(s.Key3) }).ToList();

Or even using them in the where clause

var q2 = Db.UD13.Where(w => Convert.ToInt32(w.Key1) = 12345).ToList();

Currently it doesn’t sound like that is possible. Both of those queries will not work as they are written.