Convert SQL to LINQ

Question

I'm trying to convert the following SQL to LINQ, but so far I'm not getting the right results.  Anyone know the best way to right this?

select p.*
from photo p
join
  (select pt.photoid
   from phototag pt
   join tag t on t.tagid = pt.tagid
   where t.description = 'keyword' or t.description = 'keyword2'
   group by pt.photoid
   having count(1) = 2) as pt on pt.photoid = p.photoid

Basically I have three tables.  One contains photo information (photo), another that contains tags that describe the photos (tag), and the last one to specify which tags go with which photos (phototag).  The search can be one or more keywords, but in the example above it's only 2.  Search is always AND.

Answer 1

Hello Greg,

 

Welcome to LINQ Project General forum!

 

If I understand the problem correctly, the photo table and the tag table are in many-to-many relationship.  The phototag table connects the photo and tag tables with two foreign keys.   Meanwhile, the photoid and tagid are multiple column primary keys of the phototag table.  Please feel free to correct me if there is any misunderstanding here. 

 

For LINQ to SQL, such many-to-many relationship is not directly supported.  We need to include the phototag table and in fact there are two one-to-many relationships in the .dbml.  You can refer to the following sample query:

===========================================================================================
            using (DataClasses4DataContext db = new DataClasses4DataContext())

            {

                db.Log = Console.Out;

                var query = from p in db.Photos

                            where p.PhotoTags.Where(pt => pt.Tag.Description == "keyword1"

                                || pt.Tag.Description == "keyword2").Count() == 2

                            select p;

 

                foreach (var p in query)

                {

                    Console.WriteLine(p.PhotoName);

                }

            }
===========================================================================================

 

For LINQ to Entities, the many-to-many relationship is supported with no payload in the phototag table.  So there is a direct many-to-many association between the photo and tag entities.  Here is the sample query for your references:

===========================================================================================
            using (TestDBEntities2 context = new TestDBEntities2())

            {

                var query = from p in context.Photo

                            where p.Tag.Where(t => t.Description == "keyword1"

                                || t.Description == "keyword2").Count() == 2

                            select p;

 

                foreach (var p in query)

                {

                    Console.WriteLine(p.PhotoName);

                }

            }
===========================================================================================

If you have any questions, please feel free to let me know.

 

Have a great day!

 

 

Best Regards,
Lingzhi Sun

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Hello Greg,

 

How is the problem?   If you need any further assistance, please feel free to let me know.

Have a great day!


Best Regards,
Lingzhi Sun

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.