Converting Cross join SQL to LINQ

Question

Hi All,

I wrote following cross join sql query in sql server 2008 and tried to convert to linq without any success:

select Currency.Code, Currency.Name, Total from Currency

cross join (select Count(*) as Total from Currency) C1

If somebody could help.

Thanks

Answer 1

Hi,

Could you try this

        public class Currency
        {

            public Currency(string code, string name)
            {
                Code = code;
                Name = name;
            }
            public string Code { get; set; }
            public string Name { get; set; }
        }

        static void Main(string[] args)
        {
            List<Currency> currencies = new List<Currency>();
            currencies.Add(new Currency("1","1"));
            currencies.Add(new Currency("2", "2"));
            currencies.Add(new Currency("3", "3"));

            var e = (from c in currencies select new { c.Code, c.Name, currencies.Count });



            Console.Read();

        }

Answer 2

Thanks Tamer,

My goal here is to write really efficient query. 

I had already tried this. Linq your wrote above is converted to the following sql:

Select code, name, (select count(*) from currency) as Count From Currency

I think this SQL is less efficient than the one I am try to convert to linq:

select Currency.Code, Currency.Name, Total from Currency cross join (select Count(*) as Total from Currency) C1

Amjad

Answer 3

Hi,

You can use this for a much efficient way.

        public class Currency
        {

            public Currency(string code, string name)
            {
                Code = code;
                Name = name;
            }
            public string Code { get; set; }
            public string Name { get; set; }
        }

        static void Main(string[] args)
        {
            List<Currency> currencies = new List<Currency>();
            currencies.Add(new Currency("1", "1"));
            currencies.Add(new Currency("2", "2"));
            currencies.Add(new Currency("3", "3"));
            int count = currencies.Count;

            var e = (from c in currencies select new { c.Code, c.Name, count });



            Console.Read();

        }

Answer 4

Thanks again Tamer,

Right now we are doing exactly the same in our web application (paging where it brings total records count & then actual records) what you suggested in your previous solution. This way it is much more efficient but still it hits the database twice:

- To bring record count of the table

- To bring actual record(s)

Imagine on our website if user(s) calls search page 10,000 times, database gets twice the hits, one to count and then to brink actual record(s) (10,000*2)

which is still not very efficient.

The query I wrote in sql and I m trying to convert into linq. Hits database once and brings expected records very efficiently. But I am unable to covert that query into linq.

Thanks

Answer 5

You can get the data in the database into a list, then write your query just depends to that list(not to database). That provides you the same result but the data will be only selected for once. In my example you can see the sample is provided with list, not dependent to db.