Using LinQPad with EF

Hi,

Im using LinQPad to view the results of my coding in C#. It speeds up development and has always worked.

I've installed the 1.80 version and still LinQPad works as expected with a direct SQL connection. If I switch to using the EF "connection" I can see the tables, etc. but I do get errors in the results.

As expected, if I do this in a test EF solution I have no problems. Anyone got this working, using EF?

Thx for your the help.

I've not tried LinqPad with EF but I've used it a lot recently with dynamic contexts and compiled Linq to Sql contexts. So, let's open it up and have a go :)

It sounds like you've set up the connection already but just to be sure:

In the left pane click Add Connection

Use a typed data context, Entity Framework, click Next

In "path to assembly" browse to NopCommerceStore\bin\Nop.BusinessLogic.dll

Choose Nop.DataModel

You also have to specify the server (I enter . here) and db (nopCommerce)

Click Test. Should say Connection Successful. Click OK and save the connection.

Do you get this far?

I just ran a quick test query and it worked for me: Languages.Select(l => l.LanguageId).ToList()

Please try and post the error if fails.

Thanks for helping with this :)

I think my mistake was the Database field above the query, it should be "NopObjectContext(.\SQLSERVER.DB)
Yes, I get 2 languages..perfect.

As a complete n00b at this, my first query was:
1.
Orders.Take (50)

Thats takes a while to run (11 seconds). At first it looks awfull. Not a grid with data but all kinds entitywrappers ?? and finally looking better I can see normal fields in between them.
Q.
How to get rid of those blue wrappers?

2.
from o in Orders
select new { o.OrderId, o.CustomerId}

This is ok.. :)

3.
from o in Orders
select new { o.OrderId, o.CustomerId, o.Customer.FullName}

Hmm.
NotSupportedException: The specified type member 'Customer' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

Q.
Why not?

Q.
When I use the Database directly I can see on the left all the tables with names like "Nop_..." and per table a + sign to view the fields, with the NopObjectContext then "Nop_"is gone but the fields are gone too?

Wow, number 1 took over a minute to run in my virtual machine and generates a huge amount of SQL.

The blue lines represent the objects and groupings returned by the query. You can't get rid of them as that's a faithful reproduction of the data being returned. maybe you need to use .Select() to return only the data you want.

I don't know the answer to 3 offhand, and I'm cooking dinner now :) Maybe someone else will be along with an answer soon, if not I'll investigate it tommorow. Seems to an issue particular with EF, that query would work in L2S I'm sure. There are Google hits for that error message.

Bump. Sorry didn't have a chance to look at this yet. Hopefully the bump will get a response for you, if not I'll be back!

I think the problem with

from o in Orders
select new { o.OrderId, o.CustomerId, o.Customer.FullName}

is that o.Customer is not an entity property or navigation property, i.e. it does not map to a database field or join, it's actually CLR code. Remember, if you reference the Nop business DLL in LinqPad you're using the classes the Nop guys have written and they are not simple data mapping classes they have plenty of business logic in them too (as the name would suggest).

The following, where we convert the Orders table to a CLR list wants to work:

from o in Orders.ToList()
select new { o.OrderId, o.CustomerId, o.Customer.FullName}

but I'm getting connnection strings errors.

I tried setting LinqPad to C# statements and then setting the connection string:
NopSolutions.NopCommerce.BusinessLogic.Configuration.NopConfig.ConnectionString = "secret";

This does work. If you run a query, however, you'll see from the Sql generated that this is a very inefficient way of doing things. uery Nop DLL loads every single attribute of every customerWith this query. I think it would be best to use the Nop DLL for all data access, or use purely Linq, and not mix and match them unless you understand the implications of doing so. (Hint: Customer names are stored in a different table to Customers).

I don't know much about EF yet so hopefully someone else who uses it with LinqPad will help you our some more (and I can learn from it too!)

It depends what you want to do but personally for simple querying I'd just use my own Linq2Sql data context on the Nop db and tap into the Nop DLL when I want to access the business logic (in fact this is what I've already done).

ajhvdb wrote:

Totally don't know with this one as I don't use the LinqPad visuals. I write something approximate to the query I want in Visual Studio using Intellisense (and sometimes with the help of Resharper) and then paste it into LinqPad for testing and tweaking.

Thanks for your updates on this..

I hope the developers of nop can join in (if they use LinQPad) and help us with this.

Because for now (with my limited knowledge about this) I don't see the advantages in using EF. Direct SQL or L2S does the job too.