Linq to entites

[ThatsIT.net.au]

I have a 3 tables the lets call them A,B,C
A and C have a many to many relationship so B is the linking table. B also
has other fields besides the foreign keys so it appears in the entity model.

I want to get all the rows in C that link to a single row in A. How do I
write the linq statement?

Thanks

 

[Mr. Arnold]

I have a 3 tables the lets call them A,B,C
A and C have a many to many relationship so B is the linking table. B also
has other fields besides the foreign keys so it appears in the entity
model.

I want to get all the rows in C that link to a single row in A. How do I
write the linq statement?

http://codingsense.wordpress.com/2009/03/08/left-join-right-join-using-linq/

I suggest you get yourself a good book on Linq.


__________ Information from ESET NOD32 Antivirus, version of virus signature database 4537 (20091023) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[Ray Marron]

I have a 3 tables the lets call them A,B,C
A and C have a many to many relationship so B is the linking table. B
also has other fields besides the foreign keys so it appears in the
entity model.

I want to get all the rows in C that link to a single row in A. How do I
write the linq statement?

SELECT c.pk
FROM c
JOIN b ON b.fk_c = c.pk
GROUP BY c.pk
HAVING COUNT(b.fk_c) = 1;

 

[Ray Marron]

SELECT c.pk
FROM c
JOIN b ON b.fk_c = c.pk
GROUP BY c.pk
HAVING COUNT(b.fk_c) = 1;

Hmm.. it just occurred to me that you may not have been using "linq" as
a misspelled synonym for "join". If so, sorry - never used it.

 

[ThatsIT.net.au]

http://codingsense.wordpress.com/2009/03/08/left-join-right-join-using-linq/

I suggest you get yourself a good book on Linq.

Yes I agree,
I did fix my query, using join, but I was under the impression that if you
have the relationships in your DB and the EM that you did not need to use
join.

 

[ThatsIT.net.au]

I'm using linq to entities.
I got my query to work using join but I was under the impression that if you
have the relationships in your DB and the EM that you did not need to use
join.

 

[Mr. Arnold]

Yes I agree,
I did fix my query, using join, but I was under the impression that if you
have the relationships in your DB and the EM that you did not need to use
join.

If you're talking about the ADO.NET Entity Framework Model, then I learned
you have to remove the relationships off the model for the entities and take
complete control of the Entity Model, if you really want to be successful in
using the EM and not struggle with it in coding against it.

The underlying relationships are still there on the database tables and the
database will step in and abort things if one does things that affect the
relationships in a wrong manner, but the model in not in the way with its
relationships that will hide fields that one needs to address and do other
weird things when updating entities based on relationships.




__________ Information from ESET NOD32 Antivirus, version of virus signature database 4537 (20091023) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[Erland Sommarskog]

Hmm.. it just occurred to me that you may not have been using "linq" as
a misspelled synonym for "join". If so, sorry - never used it.

Still your query puts the finger on an important point. If this is
straightforward to write in SQL, what's the point with LINQ? OK, so
with LINQ to entities, you can be more portable against different DB
engines.

It would be interesting to see the SQL that LINQ generates for the
query that ThatsIT wrote in LINQ.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Mr. Arnold]

Still your query puts the finger on an important point. If this is
straightforward to write in SQL, what's the point with LINQ? OK, so
with LINQ to entities, you can be more portable against different DB
engines.

No, that's not the point that it's striaght forward to write in T-SQL, or
that an ORM can be used againt different databases. The point is that Linq
is addressing objects right out the gate, not a returned resultset, which
leads to OOP and the power of any OOP lanuage such as .NET to use objects.


What is Object-oriented-programming?

(OOP) is a programming paradigm that uses "objects" and their interactions
to design applications and computer programs.

The key concepts of OOP are the following:

Class

Object

Instance

Method

Message passing

Inheritance

Abstraction

Encapsulation

Polymorphism

Decoupling



http://en.wikipedia.org/wiki/Object-oriented_programming

No matter what development platform Java, .Net or others OOP is OOP.

http://math.hws.edu/eck/cs124/downloads/OOP2_from_Univ_KwaZulu-Natal.pdf


What is Language Integrated Query?

LINQ is a Microsoft .NET Framework component that adds native data querying
capabilities to .NET languages.

Microsoft LINQ defines a set of query operators that can be used to query,
project and filter data in arrays, enumerable classes, XML, relational
database, and third party data sources. While it allows any data source to
be queried, it requires that the data be encapsulated as objects. So, if the
data source does not natively store data as objects, the data must be mapped
to the object domain. Queries written using the query operators are executed
either by the LINQ query processing engine or, via an extension mechanism,
handed over to LINQ providers which either implement a separate query
processing engine or translate to a different format to be executed on a
separate data store (such as on a database server as SQL queries). The
results of a query are returned as a collection of in-memory objects that
can be enumerated using a standard iterator function such as C#'s foreach.

Many of the concepts that LINQ has introduced were originally tested in
Microsoft's C? research project. LINQ was released as a part of .NET
Framework 3.5 on November 19, 2007.

http://en.wikipedia.org/wiki/Language_Integrated_Query


__________ Information from ESET NOD32 Antivirus, version of virus signature database 4538 (20091024) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[Erland Sommarskog]

No, that's not the point that it's striaght forward to write in T-SQL, or
that an ORM can be used againt different databases. The point is that Linq
is addressing objects right out the gate, not a returned resultset, which
leads to OOP and the power of any OOP lanuage such as .NET to use objects.


What is Object-oriented-programming?

I know what object-oriented programming is.

And of course, if you have a really fantastic screwdriver, you want to
use it for everything, even to plug relational nails. Even if the
performance penalty can be griveous and you have little control over
the SQL code that is generated.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Erland Sommarskog]

I am going to tell you again Linq is not about Linq-2-SQL or even the
ADO .NET Entity Framework, which are two aspect where Linq can be used.

Linq is about querying where in order for Linq to query, things must be
encapsulated in objects.

Here is a Linq Web service provider example where Linq is being used to
query the Linq Web service provider to bring information to the client
in the from of objects or objects of information in a collection.

http://msdn.microsoft.com/en-us/library/bb892929.aspx

You with the fantastic screwdriver analogy. And you say you know what
OOP is about, huh?

I learnt to program in Simula, long before O-O became the buzzword of the
say. Chance had it later that I would mainly work with relational databases.
Where for various reasons the object-oriented paradigm has not had equal
success.

I've heard this jazz about removing object-relational impedance, but I'm
afraid I'm skeptic to that LINQ is successful to that end. Your code may
look prettier, and unless you are the one who builds the entity model
and needs to see those pesky database tables, the programmers may not
even have to bother about the relational model.

And, well, as long as your performance is good, there is no reason to
lament. But there are few tools that are so powerful to make things to
really slow like a relational database engine, so chances are good
that you sooner or later hit a performance problem. And to address this
you need a good understanding of the DB engine and how it is best used.
Or you need to talk to a DBA who can understand your LINQ and entity
model. And now the impedance is back in town, just in a different
place.

Note here that I'm not saying that "relational" is better than "object-
oriented". There are simply two realms, and that is something we have
to live with. The performance issues typically manifest themselves in
the relational realm, so it's risky trying to shy away from it.

--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Mr. Arnold]

I learnt to program in Simula, long before O-O became the buzzword of the
say. Chance had it later that I would mainly work with relational
databases.
Where for various reasons the object-oriented paradigm has not had equal
success.

I started programming prosessionally in 1971 well before any of this was in
place.

I've heard this jazz about removing object-relational impedance, but I'm
afraid I'm skeptic to that LINQ is successful to that end. Your code may
look prettier, and unless you are the one who builds the entity model
and needs to see those pesky database tables, the programmers may not
even have to bother about the relational model.

What is it that I have explained to you what LINQ is about that you don't
understand and OOP, and I am NOT talking Linq-2-SQL?

And there are times when I have used SQL Command Object with T-SQL inline or
in a sproc to query the database with ADO .NET Entity Framework in the same
solution. It depends upon the needs. But no matter what type of mechanism I
use to query the database with results returned, the result is encapuslated
in an object or objects.

The entity on the model is an object. Therefore, I can use the entity
instaciated new and fill the entity as an object, even if the query was
using T-SQL and a datareader on a table that was also on the model, not
likely to happen that way but it can, or I use a DTO a Data Transfer Object
and return the DTO.

If the objetcs are in a collection, then I can use Linq-2-Object againt the
collection of objects in memory and query it, never having to go back to the
database.

And, well, as long as your performance is good, there is no reason to
lament. But there are few tools that are so powerful to make things to
really slow like a relational database engine, so chances are good
that you sooner or later hit a performance problem. And to address this
you need a good understanding of the DB engine and how it is best used.
Or you need to talk to a DBA who can understand your LINQ and entity
model. And now the impedance is back in town, just in a different
place.

What one needs to know is what type of query to use and the resulting T-SQL
that's going to be produced, which there are tools that allow the programmer
to look at the results. This would allow a DBA to say not to use the query
and formulate it another way for faster results. It could be that lazy
loading is needed instead of a fast loading query. There are options and
paths a programmer can take in querying the database with returned results
and speed.

Note here that I'm not saying that "relational" is better than "object-
oriented". There are simply two realms, and that is something we have
to live with. The performance issues typically manifest themselves in
the relational realm, so it's risky trying to shy away from it.

I don't think there is a speed issue in the ORM solution if the ORM lets the
programmer take complete control by removing the associations off of the
Entity Model that were derived from the database and its tables. ADO .NET
Entity Framework allows the programmer to do just that, which is what I use.

And if one needs speed against the model, one is going to use ESQL not
unlike T-SQL and not use Linq-2-Objects against the model. Those two ways of
doing things against the model gives the speed for the query with complete
control of the model.

I'll toss my C#.net MCP out there.

But below is more of my expertise now with SOA and where I am geared
towoards, which concerns speed, design patters, unit testing and OPP.

http://www.dofactory.com/Framework/Framework.aspx








__________ Information from ESET NOD32 Antivirus, version of virus signature database 4539 (20091024) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[ThatsIT.net.au]

I learnt to program in Simula, long before O-O became the buzzword of the
say. Chance had it later that I would mainly work with relational
databases.
Where for various reasons the object-oriented paradigm has not had equal
success.

I've heard this jazz about removing object-relational impedance, but I'm
afraid I'm skeptic to that LINQ is successful to that end. Your code may
look prettier, and unless you are the one who builds the entity model
and needs to see those pesky database tables, the programmers may not
even have to bother about the relational model.

And, well, as long as your performance is good, there is no reason to
lament. But there are few tools that are so powerful to make things to
really slow like a relational database engine, so chances are good
that you sooner or later hit a performance problem. And to address this
you need a good understanding of the DB engine and how it is best used.
Or you need to talk to a DBA who can understand your LINQ and entity
model. And now the impedance is back in town, just in a different
place.

Note here that I'm not saying that "relational" is better than "object-
oriented". There are simply two realms, and that is something we have
to live with. The performance issues typically manifest themselves in
the relational realm, so it's risky trying to shy away from it.

you lost me here,

to me a relational database is the basis for OOP.
Each table becomes a Object,
its fields become object properties
its rows are instances of the object
1 to many relationships in database is expressed in collections of child
objects of the parent object

although using linq enables you to handle data as objects, the real idea of
OOP is that the entities in your business modal are treated as objects. A
hotel booking system would treat a room as a object, its beds as child
objects of the room and so on. This is what OOP is all about in my book.

 

[Mr. Arnold]

you lost me here,

to me a relational database is the basis for OOP.
Each table becomes a Object,
its fields become object properties
its rows are instances of the object
1 to many relationships in database is expressed in collections of child
objects of the parent object

although using linq enables you to handle data as objects, the real idea
of OOP is that the entities in your business modal are treated as objects.
A hotel booking system would treat a room as a object, its beds as child
objects of the room and so on. This is what OOP is all about in my book.

The use of OOP in the solution allows this too.

What are design patterns?

Design patterns are recurring solutions to software design problems you find
again and again in real-world application development. Patterns are about
design and interaction of objects, as well as providing a communication
platform concerning elegant, reusable solutions to commonly encountered
programming challenges.

http://www.developer.com/design/article.php/1502691

http://www.dofactory.com/Patterns/Patterns.aspx

http://computerprogramming.suite101.com/article.cfm/patterns_and_antipatterns

http://msdn.microsoft.com/en-us/library/ms954638.aspx

http://www.designpatternsfor.net/Presentations.aspx?tid=3&cid=4



What is Domain Driven Design?

(DDD) is an approach to the design of software, based on the two premises
[1] that complex domain designs should be based on a model, and that, for
most software projects, the primary focus should be on the domain and domain
logic (as opposed to being the particular technology used to implement the
system).

http://en.wikipedia.org/wiki/Domain-driven_design



What is Test Driven Design?

(TDD) is a software development technique that uses short development
iterations based on pre-written test cases that define desired improvements
or new functions. Each iteration produces code necessary to pass that
iteration's tests. Finally, the programmer or team refactors the code to
accommodate changes. A key TDD concept is that preparing tests before coding
facilitates rapid feedback changes. Note that test-driven development is a
software design method, not merely a method of testing.

http://en.wikipedia.org/wiki/Test-driven_development

http://weblogs.asp.net/rhurlbut/archive/2007/07/16/another-tdd-and-ddd-success-story.aspx



What is Model –View- Controller?

(MVC) is an architectural pattern used in software engineering. Successful
use of the pattern isolates business logic from user interface
considerations, resulting in an application where it is easier to modify
either the visual appearance of the application or the underlying business
rules without affecting the other. In MVC, the model represents the
information (the data) of the application; the view corresponds to elements
of the user interface such as text, checkbox items, and so forth; and the
controller manages the communication of data and the business rules used to
manipulate the data to and from the model.

http://en.wikipedia.org/wiki/Model-view-controller

http://msdn.microsoft.com/en-us/library/ms978748.aspx

http://weblogs.asp.net/scottgu/archive/2007/10/14/asp-net-mvc-framework.aspx

http://cristobal.baray.com/indiana/projects/mvc.html

http://www.devx.com/dotnet/Article/29992/0/page/1



What is Model –View- Presenter?

MVP is a software pattern considered a derivative of the
Model-view-controller.

http://en.wikipedia.org/wiki/Model_View_Presenter

http://msdn.microsoft.com/en-us/magazine/cc188690.aspx

http://mrrask.files.wordpress.com/2008/01/model-view-presenter.pdf

http://www.mvcsharp.org/Reworking_ASPNET_MVC_Store/Default.aspx

<http://www.codeproject.com/KB/aspne...lect=2822806#Reusing the presenter in windows>

<http://codebetter.com/blogs/jeremy....net-and-the-model-view-presenter-pattern.aspx>



MODEL-VIEW-PRESENTER

http://www.polymorphicpodcast.com/

click 'Shows'

click 'Design Patterns Bootcamp: Model View * Patterns*

view parts 1-5


__________ Information from ESET NOD32 Antivirus, version of virus signature database 4540 (20091025) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com